EXCEL VBA

Excel-VBA よく使うコマンド集のまとめ、あんちょこ、虎の巻

EXCEL VBA

こんにちは! 健史です。

Excel-VBAのプログラムを作成する上で、いくつかのコマンドをノートに書き記し"いつでも見られるように"してあります。

理由は、プログラムの作成頻度は低く時々であるために、しかし作成するときに比較的頻繁に使うコマンドであるものの、覚えきれなかったり忘れてしまうものがあるからです。

更に自分のPC以外で作業することがあり、そんなとき迅速に対応したいため普段から持参しています。

スポンサーリンク

よく使うコマンド集

分岐と改行:If,Selectなど

If i <> 0 and _
    j > 0 Then
    MsgBox "TEST"
End If

Select Case strName
    Case "りんご"
       MsgBox "Apple"
    Case Else
       MsgBox "???"
End Select

Select Case inPoint
    Case Is >= 80 
       MsgBox "80イジョウ"
    Case Else
       MsgBox "???"
End Select

Select Case inPoint
    Case 80 To 100 
       MsgBox "80~100"
    Case Else
       MsgBox "???"
End Select

ループ:For,Whileなど

Dim i As Long
For i = 1 To 10

Next

i = 1
Do While i < 11 'または Do Until i > 10
    i = i + 1
Loop

i = 1
Do
    i = i + 1
Loop While i < 11 'または Do Until i > 10 

セルの最大有効値検索:xlDownなど

Dim last_row1 As Long
max_last1 = Worksheets(1).Range("A1").End(xlDown).Row

Dim last_col1 As Long
last_col1 = Worksheets(1).Range("A1").End(xlToRight).Column

複数のファイルを扱う

'定義
    Dim excel0, excel1, excel2 As Workbook

'マクロ自身をexcel0にセット・・・(項目定義後の)処理の最初に記述
    Set excel0 = ActiveWorkbook

'入力ファイルや参照ファイルをオープンと当時にexcel1にセットする場合
'strPath1には[C:\temp01\file01.xlsx]などのファイル名がセットされている
    Set excel1 = Workbooks.Open(strPath1)

'新規にファイルを開くと同時にexcel2へセットする場合
    Set excel2 = Workbooks.Add
'新規に開いたファイルの保存
'strPath2には[C:\temp01\file02.xlsx]などのファイル名がセットされている
    excel2.SaveAs strPath2
    excel0.Save

'参照は、先頭にワークブック名を付けるだけ
    If excel0.Sheets(1).Cells(1,2) = "" Then

ブックやシートをアクティブ:Activate

Workbooks("Book2.xlsx").Activate

Worksheets("Sheet2").Activate

Workbooks("Book2.xlsx").Sheets("Sheet2").Activate

シート全体、行、列のクリア:Clear

Worksheets(2).Cells.Clear             'シート2全体
Worksheets(2).Rows(2).Cells.Clear     'シート2の2行目
Worksheets(2).Columns(5).Cells.Clear  'シート2の5列目(E列)

・Clear:全て
・ClearFormats:書式
・ClearContents:数式、文字列
・ClearComments:コメント文
・ClearOutline:アウトライン

フォルダからファイル名を取得

    Const cnsDIR = "\*.*"
    Dim strPath As String
    Dim strFilename As String
    strPath = "c:\temp1"
'最初のファイル名を取得
    strFilename = Dir(strPath & cnsDIR, vbNormal)
    Do While strFilename <> ""
         MsgBox "FILE-NAME=" & strFilename
'次のファイル名を取得
         strFilename = Dir()
    Loop

テキストファイルの入出力

    Dim strPath1, strPath2 As String
    Dim strTxt1 As String
    strPath1 = "c:\temp01\dataIn.txt"
    strPath2 = "c:\temp01\dataOut.txt"
    
    Open strPath1 For Input As #1
    Open strPath2 For Output As #2

    Do Until EOF(1)
        Line Input #1, strTxt1
        Print #2, strTxt1
        Print #2, ””           "改行
    Loop
    Close #1, #2

シートの数、最大値

Dim sht As Long
sht_max = Worksheets.count

'1番目のシートに2番目以降の全シート名を取得する
'※注意! 1番目のシートにデータがないこと
Dim sht As Long
For sht = 2 To Worksheets.Count
    Worksheets(1).Cells(sht, 1) = Worksheets(sht).Name
Next
Worksheets(1).Cells(1, 1) = "シート名"

Excel実行時の画面遷移を表示させない

'表示させない
Application.ScreenUpdating = False
'表示させる
Application.ScreenUpdating = True

Excel実行時の指定のセルに画面を遷移させる

Application.Goto Reference:=Sheets(1).Cells(ix1, iy1), Scroll:=True

エラーが発生したときなど、上記コマンドで該当箇所に遷移したのち、[MsgBox]でエラー内容を表示させるなどセットで使うと効果的です。

ただし、上記[9.]で「Excel実行時の画面遷移を表示させておく設定」で実行します。

削除などの確認メッセージを表示させないで強制実行する

例えば、シートを削除するコマンドを実行しようとすると「このシートは完全に削除されます。続けますか?」と確認メッセージが表示されます。

確認メッセージを表示させないようにする方法です。

'確認メッセージを表示させない
Application.DisplayAlerts = False

'確認メッセージが表示されるコマンド
'「Sheets(1).Delete」など

'確認メッセージを表示させるように戻す
Application.DisplayAlerts = True

最後に

ノートに転記するのはたいへんですから、メモ帳やExcelに貼り付け、加工編集・印刷し、また更にカットして活用するものひとつです。

職場で自分に割り当てられたパソコンにて、コピー&ペーストして使いやすいように加筆・修正や不足していいるコマンドなどを追加しデスクトップに貼り付けておくことをお勧めします。


私の場合は、整理したフォルダの直下において置き、デスクトップにはフォルダのショートカットを置いてアクセスしています。

パソコン作業での無駄な時間、後戻り、リスクを少なくする使い方
パソコンを使い始めて20年以上経ちます。ファイルを誤って消去してしまったり格納したファイルを探すのに時間が掛かる等、後戻りや無駄なことが多々ありましたが、今は工夫してロスやリスクを軽減しています。目新しいことでないかもしれませんがご一読を。

今後、利用可能な環境であれば、この記事を含むExcl-VBAの記事にアクセスしようと思います。

コピー&ペーストすればミスを少なく、迅速に対応できますので。

EXCEL VBA
スポンサーリンク
- 面白かったらシェアお願いします! -
健史をフォローする
自分で改善

コメント