VBA 手間を惜しむな。何したか 画面に出そう。

VBAプログラムを終了したら何か表示させよう

例えば、ファイルを出力するなら、どこのディレクトリに何のファイルを出力したか？
安心感が違います。

dim tmp as string

tmp = "ディレクトリ：（"　& workPath & "）に　ファイル：（" & fileName & ") を出力しました。"　
MsgBox (tmp)

いったんtmpに入れているのは　debug.print tmp をしたりデバック時に判るようにです。

何も表示せずに終了させるのですか？いつ終わったのだろうか？勘弁してください。

Excel VBA Cells の Colは数字はやめよう

Cells (3, 14) のカラムはどこだ？
Cells (3,"J" ) のほうが１目でわかるぞ
　　メンテも楽だし。。。。

　　本当はマジックナンバーを使いたくないが、、、

VBA Sub Moduleは2画面程度に抑える

 　だらだらと3画面も続くようなプログラムはやめましょう。
　ある程度の塊で意味を持たせて sub に納めます。

　といいますか、いきなりコーディングせずに手順書を日本語！で書きます。
　大まかな手順ごとに　面倒がらずにSub Module化します。
　後から絶対見やすいです。

例えば TOPは以下のように
　sub main()
        dim r as long
        dim c as long
          …
          …
        Call A
        Call B
        Call C
        Call D
    end sub

Call しかない場合が素敵です。

　同じ処理をCopy Pasteして2回書いてもいけません。必ずSub Module化します。
　たとえ数行でもです。
　Bug修正時に絶対もう一方の修正を忘れます。
　差分は 引数で分けます。

[VBA]インデントを深くしない技

if 文を重ねるとインデントが深くなってしまいます。3つ以上のインデントは避けるべきです。
そのための手法があります。ちょっと慣れが必要ですが。
sub aaa
    if a=1 then
        xxx
        xxx
        if b= 2 then 
            yyyy
            yyyy
        end if
    end if
end sub

の場合は以下のように記述します。

sub aaa
    if a != 1 then
        exit sub   ' 何もしない
    end if

    xxx
    xxx
    if b=2 then
        yyyy
        yyyy
   end if
end sub


   aが1以外の場合何もしないので
　条件を反転して　何もしなくて　sub を抜けるようにします。
　xxxが2行しかありませんが、体外20行ぐらいあったりします。
　その場合20行先で、何もしないことが分かります。
　ならば、最初にわかるようにすると親切です。

Excel VBA セルのループは r , c を使おう

ループは　i,j が使われることが多いですが、Excelに関しては
Cells(Row,Column)で示されますので、行(Row) , 列(Column)の rとc で示したほうが
判りやすいです。

For i = 1 to 100
  For j=1 to 100
     Cells(i,j ) = i
  Next
Next

i,jだと、行か列かが計算途中でわからなくなることがあります。

Dim r as Long
Dim c as Long

For r = 1 to 100
  For c=1 to 100
     Cells(r,c ) = r
  Next
Next

r, c のほうが読みやすいです。
ちなみに r, c は Longで宣言しておきましょう。32,767の壁を越えられなくなります。

タグ：VBA

Exel VBA コメントのショートカットの変更

VBAでコメントアウトする際にショートカットを設定すると便利です。



マウスで右上の　コメントボタンを押せばいいのですが、マウス操作なしにできると少し便利になります。

例えば、下図のように　Alt+/に設定するためには、選択したボタンの編集から
名前を　(&/) とし イメージとテキストを表示にします。



これで　Alt+/で カーソル行がコメントになります。
コメントを戻すショートカットは　(&?) に設定しています。
Alt＋Shift＋/ でコメントを戻すことが出来ます。

少し便利になりました。

参考図書：ExcelVBAを実務で使い倒す技術

[Excel VBA]変数命名記法 キャメル記法

変数宣言で様々な書き方をしてきましたが、やっと巡り合った記法に決めました。

変数：キャメル記法　単語の連接時に2目の先頭を大文字にします。
定数：スネーク記法　すべて大文字で単語の連接は アンダースコア '_'　を使う

例えば）
Dim row1st as Long
Dim col1st as Long
Dim rowEnd as Long　　
Dim colEnd as Long

Const NEW_FILENAME =”新しいファイル名"

その他に、関数で戻りがBoolean の場合、次のように決めるのが判りやすそうです。
Function isXxx 存在するかどうか
Function hasXxx, Xxxを持つかどうか
Function canXxx Xxx が実行できるかどか

変数の区切りのアンダースコアが嫌いだったので、キャメル記法で書きましょうと指定できるので、すっきりしました。
　
　なぜ、アンダースコアが嫌いか？？？
　１０１（英語）キーボードは日本語のアンダースコア（右下）のキーががなくて
打てないからです。いちいち英語モードに戻さんと打てない。。。
　なので、キャメル記法はありがたい。

タグ：キャメル記法 EXCEL VBA

Excel VBA 関数は作るな。ありもの探せ

やりたいことを 自力で作ると、後でわからなくなる。
ありもののVBA 関数を使おう。Google するとすぐに判るのに。

例えば
Now に30分加えて 時間のところを取り出して、2桁にする。

dim a as string

a = Format (Hour(DateAdd("m",30,Now)),"00")

いずれもVBAの関数です。
Format関数は何気に便利です。

[Excel VBA]VBA ツール・オプションの初期設定を変更すべき

Excel VBAは必ず先頭で
Option Explicit
宣言を書いて、変数宣言を強制しよう。
この文字がないVBAはメンテする気が起きません。
中級者でもVBAの初期設定のまま、宣言せずに使っている馬鹿者がいます。勘弁してほしい。
なので、ツール・オプションの設定で
　　変数の宣言を強制する　にチェックを入れておきます。
　　ついでに自動構文チェックは外しておきます。



自動構文チェックは編集途中で改行した際に、エラーメッセージのポップアップが現れ、鬱陶しい。
これが解放されます。エラーはそのまま赤文字のため、なんの問題もありません。

初級者には必ずこの設定をしてもらうとよいでしょう。

タグ：Excel,VBA,Option Explicit

[Excel VBA] 文字列検索し、その結果をAddressかValueで返す。オフセット付き

文字列を検索し、見つかったRangeを戻す関数。
オフセットを入れられるようにして、見つかった文字から RowでLoopさせたりするときを目的とした関数。
アドレスが欲しい場合、Rangeで戻しているので、
 fnFind("検索文字").address
が使える。

Function fnFind(KeyWord As String _
        , Optional ofstR As Long = 0, Optional ofstC As Long = 0) As Range
    ' 2020/10/24 Sanono
    ' 文字列を検索し、見つかったセルを Rangeで返す。
    '　アドレスが必要な場合　.adderss をつける。指定がない場合Valueが戻る
    ' Row,Columnのオフセットを指定できるようにして、
    ' 検索文字からのRow,ColumnでLoop用いることを想定
    
    ' Keyword       : 検索文字
    ' area          : 検索範囲
    ' ofstR         : Row    方向のオフセット
    ' ofstC         : Column 方向のオフセット
    
    Dim rng As Range
    Dim ret As Integer
    Set rng = Cells.Find(What:=KeyWord, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
        , MatchByte:=False, SearchFormat:=False)

    If rng Is Nothing Then
        ret = MsgBox("検索文字[" & KeyWord & "]が見つかりません、終了します。" & vbCrLf _
            & "　キャンセルでデバックモード", vbOKCancel)
        If ret = vbOK Then
            End
        End If
        Stop
        Set fnFind = rng
    Else
        Set fnFind = rng.Offset(ofstR, ofstC)
    End If

End Function

Sub test_fnFind()
    Dim tmp As String
    tmp = fnFind("A2")
    Debug.Print tmp
    Debug.Print fnFind("A2").Address
    Debug.Print fnFind("X2")
End Sub

タグ：Excel,VBA