VBAブック制御

PART6．旧HPの注目内容を抜粋仮保存

過去に作成したHPに記載事項で、今後も役に立つと思われる内容はPART1～PART5に集約する予定ですが、旧HP廃棄に当り見直したらまだ未折込の内容がありました。近い将来の暇な時マクロ、挿絵ともExcel2010対応版に更新し、PART1～PART5へ織り込みたいと思います。

折込が何時になるか判らないので、必要と思われる内容を取りあえずここに転記しました。これで思い残すことが無くなったので15年親しんだ旧HPを消去します。

6-1Excel5・Excel95HPから残し

6-1(1) ブックをチェックしオープン又はアクティブ化


Const fil2 As String = "b:\VBA雑\小文字.xls"
Const fil2a As String = "小文字.xls"
Sub 例31()
    fila = 0
     For Each file_name In Windows
        If file_name.Caption = fil2a Then
             Windows(fil2a).Activate
            fila = 1
        End If
    Next
        If fila = 0 Then
           Workbooks.Open Filename:=fil2
        End If
End Sub

・このプログラムでは定数を使用しファイル名を入れているが、システムに合わせ変更する。

・Ｆｏｒｅ　ＥａｃｈでＥｘｃｅｌでオープンされているウインドウを調べる。

・Ｉｆ　文で目的のファイルがあるかチェック無い場合はオープンする。（なお、ファイルが無い場合は「＊＊が見つかりません」とExcelのダイアログ表示）

6-1(2)シートがあるかチェックしなければ追加する

マクロ実行前

本マクロ実行後


Sub 例32()
    sck = 0
    For Each sheet_name In Worksheets
        If sheet_name.Name = ("検索結果") Then
            sck = 1
            Exit For
         End If
    Next
'
    If sck = 0 Then
       Sheets.Add.Name = "検索結果"
    End If
End Sub

・Ｆｏｒ　Ｅａｃｈでブック上にあるシート名を変数Sheet_Maneへ入れ、Ｉｆ文でシート名「検索結果」があるかチェックしある場合はExit　ForでＦｏｒ文を抜ける

・シートが無い場合変数"sck"は０であり、シートを追加し名前を付ける。

6-1(3) 　フォルダ－の作成と削除
ある特定のフォルダ－へマクロ処理したファイルを入れる場合、下記マクロでフォルダ－を作成できる。（作成しようとした時、既にあるとエラ－になるので事前チェックが必要）


(1) フォルダ－の作成
Sub 例316k1()
phn = ActiveWorkbook.Path
    On Error Resume Next
        MkDir phn & "\MyGIF"
    If Err = 75 Then
        On Error GoTo 0
        Exit Sub
    End If
       On Error GoTo 0
End Sub

'(2)フォルダ－の削除
Sub ki001b()
phn = ActiveWorkbook.Path
    On Error Resume Next
       RmDir phn & "\MyGIF"
    On Error GoTo 0
 End Sub

削除しようとした時、指定したフォルダ－が無いとエラ－になるので、事前にチェックするかエラ－処理(上記はエラ－処理）が必要。

6-1(4) 保存場所を取得し別のファイルもそこへ保存

１度も保存していないファイル(メニューの[ファイル][新規作成]を行なった場合等)はまだパスがないので下記例のような操作が必要


Sub 例1434()
   phn = ActiveWorkbook.Path
       If phn = "" Then
         phn = Application.GetSaveAsFilename(initialfilename:=dai)
       End If
   Name "C:\test\Book1.xls"  As phn & "\Book1.xls"
End Sub

１度保存してあるファイルは、"phn = ActiveWorkbook.Path"でパスを取得出来る。パス未設定の場合は上記のダイアログで設定出来る。（なお、このダイアログは保存を実行しない）

6-1(5) 文字変換（日→英）方法

下記、表１の日本語色名を、表３のリストを参照し表２へ変換するマクロ例

表1.元のリスト		表2.変換後リスト		表3.参照リスト

[1] 関数「VLOOKUP」を使用して、日本語を英語に変換する方法


    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight
    
    Range("P2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[1],[zidou.xls]Sheet3!R1C1:R46C2,2)"
    Range("P2").Select
       Range(Cells(2, 16), Cells(cen1, 16)).FillDown
            
    Columns("P:P").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

    Columns("Q:Q").Select
    Selection.Delete Shift:=xlToLeft

[1]Ｐ列に１列挿入し、[2]挿入した列（再度Ｐ）へ計算式を入れる（これは自動記録で作成したが、参照リストはブック名zidouのSheet3、A1～B46）
[3]計算式をＰ列全部へコピー、[4]セル内の結果だけを残し式を削除
[4]日本語列（本例はＱ列）を削除。

VLOOPKUP関数使用の注意点：
[1]参照リストの１列目のDATAは、文字列・数値・理論値とも可
[2]英字の大文字と小文字は区別されない
[3]参照リストは、コード順の昇順になっていること。(これは重要であり
　　[データ]→[並び変え]→[昇順]を実施）正しくないと誤検出する。

[2] Findメソッドを使用して変換(エラ－を回避）
上記は、デ－タ無しの場合もセルをアクティブにしておりエラ－となる。デ－タ無しは"Nothing"が返るので、それを利用すると下記のようにエラ－を回避したマクロを作ることが出来る。


For i = 2 To cen1
      jp = Cells(i, 5)
      Windows(fil1).Activate
      Sheets("Sheet3").Select

	Set actv = Range("A1:A200").Find(jp, , , xlWhole, xlByColumns, xlNext, False)
	　　If actv Is Nothing Then
             　eng = jp
          　Else
              actv.Select 
            　ra = ActiveCell.Row
            　eng = Cells(ra, 2)
         　 End If
 
      Windows(fil2).Activate
        Cells(i, 5) = eng     
Next

6-1(6) グラフ・パレ－ト図の作成

(1).このプログラムの使用方法

[1]ワ－クシ－トにデ－タ作成

・左図の水色部分(Ａ列・Ｂ列）へ任意のデ－タを入れる
・行数はマクロで見ているので特に制限なし（気にしなくてよい）
・数値は大きい順にマクロで並び替えるので気にしなくてよい
　(ただし、最終行に”他”の文字がある場合はその行は並び替え対象外)
・総数はマクロで自動計算
・Ｃ列のパ－セントはマクロで自動計算

[2]上記シ－トをアクティブの状態でマクロを実行

上図のようなパレ－ト図が出来る。
（なお"N=**"の総数はシ－トの別な個所に表示されるので自分で移動すること）
（このマクロはシ－トにグラフが一個の条件で作成してあり既にグラフが表示させている場合は消去してから実行して下さい）

(2)マクロ表示
Netscape4.0以降（ExpolrerもOK）ではWebブラウザ上の文字をそのままコピ－出来る。
ここをクリックでマクロソースが表示されるので
Excelマクロシ－トに貼付け、自由に活用して下さい。

(3)その他(このマクロは使用した方からお礼のメ－ルが来た： 99/10/10　Ｆさんより）

半信半疑で実行したら感動しました。なんか良く分かりませんがマクロの素晴らしさを痛感しました。

パレート図は会社で良く使います。報告書・QC・・・など
今の心境は何とかしてマクロをホームページで勉強して使えるようになりたいと思います。ありがとうございました。

【戻る】　　　　【Top画面】　　　【HPへ】