マクロの裏技

ＥｘｃｅｌＶＢＡを使っていると、いろいろな裏技に出会います。ここでは、それらを忘れないようにまとめてみました。Ｗｉｎ坊が発見したものだけでなく、他人から教わったものもあります。マクロは奥が深く、日々勉強ですね。

ワークシート関数を、ワークシートで使ってVBAで利用する

　ワークシート関数は、Application.WorksheetFunction.関数名( )　と指定することでVBAの中で使うことが出来ますが、このように使えない関数もあります。検索用の関数がそうですが、簡単に本日の日付を返す =TODAY() なども　Application.WorksheetFunction.TODAY()　とは使えません。そのような場合には、ワークシートのセルにこの関数を書いて、その値を　Cells(行,列)　でVBAの中に取り込むことで使います。例えば、この関数をJ15に書いたとすると、この値をVBAの中で使いたいときに　TODAYDATE＝Cells(15,10)　とします。これで変数TODAYDATE（名前は任意）に日付のシリアル値が取り込まれます。

万能検索

検索関連のワークシート関数は、Application.WorksheetFunction　で使えないものが多いので、VBAにはVBAの専用の検索関数を使います。以下のパターンを覚えておくと、シート内のデータを検索してくれます。

Dim YLine As Long
Dim XLine As Integer
Dim Obj As Object
smoji = InputBox("検索文字を入力してください")
Set Obj = Sheet1.Cells.Find(smoji)
If Obj Is Nothing Then
MsgBox smoji + "は見つかりませんでした。"
Else
YLine = Sheet1.Cells.Find(smoji).Row
XLine = Sheet1.Cells.Find(smoji).Column
MsgBox smoji + "は、" + CStr(YLine) + "行目の" _
+ CStr(XLine) + "列目にあります"
End If
この例は、InputBox　と　MsgBox　を使っていますが、要するに　smojiという文字列データを、sheet1(シート見出しの名前ではありません)の中で探して、見つかったらそのセルの行(YLine)と列(XLine)の番号を返してくれるものです。

エラー値があるときの処理

matchなどの検索系の関数を使うと、結果が＃N/Aなどになる場合があります。このようなセルの参照を含むマクロを組むときに、エラー値は特別な値になるので、ｉｆ分などで除外しておかないとマクロが止まってしまいます。このような場合は、ＩｓＥｒｒｏｒの値を条件にします。
例えば、Cells(I,J)　の値を調べる場合は、以下のような条件文を書きます。

　If IsError(Cells(i, j).Value) Then
　　'エラーの時
　Else
　　'エラーでない時
　End If

エラーの種類で分岐したい場合は、

If IsError(ActiveCell.Value) Then
errval = ActiveCell.Value　　として、errval の値を取得します。この値は、CVErr(**)　の引数を以下のように指定した値になります。
これが、#DIV/0! エラー　のときは　CVErr(xlErrDiv0)　　#N/A エラー　のときは　CVErr(xlErrDiv0)
#NAME? エラーは、CVErr(xlErrName)　　#NULL! エラーは、CVErr(xlErrNull)　　#NUM! エラーは、CVErr(xlErrNum)
#REF! エラーは、CVErr(xlErrRef)　　#VALUE! エラーは、CVErr(xlErrValue)　　となります。

特定のセルに入力したときに、マクロを自動実行させる

　マクロを実行するのには、ボタンをを配置して、これにマクロを登録するか、ショートカットキィを登録してキィボードから指示するか、またはツールメニューのマクロから指定します。マクロの存在を意識せずに、セルの値が入力されたら自動的に実行させたい、という場合には、モジュールシート以外の場所に、特定のマクロを書き込みます。マクロは、Ｍｏｄｕｌｅシート以外にもかけます。

普通に、モジュールシートに、ｋｅｉｓａｎというマクロを作りました。これは、Ａ1（ｃｅｌｌｓ（1,1））の値を読み取って、これを2倍して1をたしたものをＢ1（ｃｅｌｌｓ（1,2））に表示するというものです。これを、Ａ1に入力があったら即座に実行させようと思います。

そこで、Module1でなく、sheet1をWクリックして、ここにコードを書き込みます。ちょっと長いですが、名前は
Private Sub Worksheet_Change(ByVal Target As Range)　とします。これと、End Sub　の間に、
If Target.Column = 1 And Target.Row = 1 Then        ' 変化を検知するセルの列数（Column）と行数（Row）を指定します
    keisan
    Range("C1").Select
End If
このＩｆ文の組は、複数書き込めるので、セルの値によって実行するマクロを選ぶこともできます。

最後の、Range（）．Ｓｅｌｅｃｔ　は、単に実行が終わったことを示すために、カーソルを移動させるものです。

このようなＰｒｉｖａｔｅ　Ｓｕｂは、Worksheet_Changeの他にもあります。
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)　　セルのＷクリックを検知します　
Worksheet_SelectionChange(ByVal Target As Range)　　セルの選択を検知します（入力が無くていい）

印刷を制御する

特定のセルの値を調べて、条件に合わないときに印刷ができないようにします。この場合は、ＴｈｉｓWorkbookを開いて
Private Sub Workbook_BeforePrint(Cancel As Boolean)　と言う名前のマクロを書きます。

上の例は、セル（1,15）の値がＯＫでないときは、ＭｓｇＢｏｘで警告を表示し、Ｃａｎｃｅｌ＝Ｔｒｕｅ　で、印刷をキャンセルします。

ヘッダー・フッターを書き込む

　　ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
　　ActiveSheet.PageSetup.RightFooter = Format(Date, "ggge年m月d日")

これは、右のフッターにセルA1の値を入れ、年号書式を設定するものです。
同様に、 LeftHeader　：　左側のヘッダー CenterHeader　：　中央のヘッダー RightHeader　：　右側のヘッダー LeftFooter　：　左側のフッター CenterFooter　：　中央のフッター RightFooter　：　右側のフッターになります。

テキストファイルに書き出す・読み込む

拡張子.ｔｘｔのファイルは、メモ帳でも編集でき、文字データを保存しておくには、最も手軽なファイルです。エクセルで、このｔｘｔファイルに書き出したり、ここから読み込んだりできると、より拡張した使い方ができますね。

読み込み：
Dim datal As String　　　　　’読み込んだ行データを入れる変数です。
txtname = "ファイル名"　　　　　’ファイル名は、パスも付けて指定します。（ C:\****\****\***.txt ）
Open txtname For Input As #1　　　’ファイルをOPENする命令です。
For i = 1 To 100
Line Input #1, datal　　　’Input　#1でも良いのですが、行内に，（カンマ）があると2行分になるのを防ぎたいときは、Line　Inputを使います。
　Cells(i,1) = datal
If datal = "// -->" Then Cells(i, 1) = "": Exit For　　　’For　Next　を使ったので、ｔｘｔファイルの終わりの文字を指定しました。
Next i
Close #1　　　　　　Openに対して、必ずClose　で　開いたファイルを閉じます。

書き出し：
txtname = "ファイル名"　　’読み込みの場合と同じです。
Open txtname For Output As #1　　　’今度は、　Output　として開きます。
For i = 1 To 100
Print #1, Cells(i,1)　　　　　’Ｐｒｉｎｔ　#1　で1行分のデータを書き出します。
Next i
Close #1　　　　　　　　　　’開いたら閉めるのが決まりです。

書出し2：追記していく場合は

Open txtname For Append As #1　で開きます。

カレントドライブの指定は、　ActiveWorkbook.Path + "\ファイル名"

エクセルを終了して、ワードを開く

ThisWorkbook.Save　エクセルを、上書き保存します。
Call Shell("WINWORD C:\**.doc", vbNormalFocus)　　または　Shell "cmd /c C:\**doc", vbHide
Application.Quit　　開いた後で終了します。

エクセルから、メールの送受信・Webサーバーからダウンロード・アップロード　するには

　普通ではできませんが、フリーで提供されているBASP21（ビーエーエスピー21）というコンポーネントをインストールすることで、VBAに機能を追加でき、その機能を使ってこれらが可能になります。実際の使い方は、（officeTANAKAさんのページ）ここにあるのでリンクをおいておきます。若干制限はありますが、大変便利なものです。

ワードファイルをを開く

fname = "WINWORD " + ActiveWorkbook.Path + "\契約書.doc"
Call Shell(fname, vbNormalFocus)

●表紙へ