Excel VBA ノート(サンプルコード)

Hide Tamura の Excel VBA ノート

Excel-VBA フラグ用の変数によりイベントをぬける(イベントをキャンセル、イベントを止める、イベントからぬける)

予測していなかったところでイベントが発生して困ることがあります。

例えば、セルの選択が変更される度にイベントが発生し、ワークシートモジュールの
Worksheet_SelectionChange というプロシージャが実行されてしまいますね。

他のマクロからセルを選択する度に、このプロシージャが動いてしまいます。

そこで、このプロシージャ内の一行目に、ある変数がTrueだったら、すぐにマクロをぬけるように書いておきます。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'フラグ用の変数がTrueの時は、すぐにプロシージャをぬける
    If blnFlag = True Then Exit Sub
    
    MsgBox "セルが変更される度にメッセージボックスが表示される", vbInformation

End Sub


標準モジュールでは、Boolean型の Public変数、blnFlag を定義しています。(変数名は自由に)

下の TEST1プロシージャは、セル範囲をコピーしてペーストしているだけです。はじめに変数にTrueにし、最後にFalseに戻しています。

Public blnFlag As Boolean

Sub TEST1()

    'フラグ用の変数を作りTrueにしておく
    blnFlag = True

    'コピーしてペーストするだけ
    Range("D5:G5").Select
    Selection.Copy
    Range("D10").Select
    ActiveSheet.Paste

    'フラグ用の変数を作りFalseに戻す
    blnFlag = False

End Sub

これで、 TEST1プロシージャ内で、セルを選択しても、Worksheet_SelectionChang をすぐにぬけてくれます。

Excel-VBA 複数のワークシート内に点在するセルの場所を管理し、管理表をもとにセルのデータを消去する

複数のワークシート内に点在するセルのデータを消去したい場合があります。

そんな時、VBAのプログラム内で、セルを指定してクリアする処理を沢山書く必要はありません。
後でセルの位置が変更になったり、増えたり、減ったりという場合に、いちいちプログラムを見直すだけで大変です。

そこで、ワークシート内に、消去したいシート名と、セル範囲(レンジ)の管理表を作ってしまいます。
その表を元に、下のプログラムを実行するだけで済みます。

vbaで消去

-----------------------------------------

'管理表のあるワークシート名「Work」を変数に入れる
'シート名が変わった時に、ここだけ直せば済む
Public Const ShtWork As String = "Work"

Sub All_DataClear()
    Dim i As Long, lngLastRow As Long
    Dim strMySheet As String, strMyRange As String
    
    '画面の動きを止めておく
    Application.ScreenUpdating = False
    
    'データ消去表の最終行を求める
    lngLastRow = Sheets(ShtWork).Cells(Rows.Count, "H").End(xlUp).Row
    
    '5行目から最終行まで処理を繰り返す
    For i = 5 To lngLastRow
    
        'シート名の取得
        strMySheet = Sheets(ShtWork).Cells(i, "H").Value
        
        '消去するレンジを取得
        strMyRange = Sheets(ShtWork).Cells(i, "I").Value
    
        '取得したシートのレンジをクリアする
        Sheets(strMySheet).Unprotect
        Sheets(strMySheet).Select
        Sheets(strMySheet).Range(strMyRange).Select
        Selection.ClearContents
    
    Next i
    
End Sub
 

Excel VBA教室 4 複数のマクロを続けて実行する(マクロを組み合わせて一つのマクロを作る)

bizvba_160
例えば、マクロ記録機能を使い、3つのマクロ Macro1, Macro2, Macro3 を作ったとします。

これを実行するのに、Macro1を実行して、次に、Macro2を実行して、Macro3を実行して・・・とやる必要はありません。

ここでは、複数のマクロを続けて実行するマクロを作ります。
 

とっても簡単なマクロですが、後に、様々な処理のマクロを組み合わせて作成する為の基本となります。


Sub Macro1()
    Range("D4").Select
    Selection.Copy
    Range("E4").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub


Sub Macro2()
    Range("D4").Select
    Selection.Copy
    Range("E5:F5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub


Sub Macro3()
    Range("D6").Select
    Selection.Copy
    Range("E6:G6").Select
    ActiveSheet.Paste
    Selection.Font.Bold = True
End Sub

これを実行するのに、Macro1を実行して、次に、Macro2を実行して、Macro3を実行して・・・とやる必要はありません。

次のように、Macro名を自由に決めて、Sub マクロ名() ~ End Sub の間に、続けて実行したいマクロ名を Call してあげます。

Sub Macroの統合()
    Call Macro1
    Call Macro2
    Call Macro3
End Sub

「Macroの統合」マクロを実行すれば、Macro1, Macro2, Macro3 が続けて実行されます。簡単ですね。

Call は次のように省略できますが、Call が付いている事で、他のマクロを呼び出していることが視覚的に分かりやすくなりますし、他のマクロを呼び出している箇所を検索する事もできるようになりますので、省略しない方が良いです。

Sub Macroの統合()
    Macro1
    Macro2
    Macro3
End Sub

マクロを分割して作成しておくと、各マクロの実行順序が変わった場合にも変更が簡単です。
また、他のマクロに共通して利用できる場合もあり便利です。

Excel-VBA Workbook_Open の中で他のブックをActivateしたい時は別のサブルーチンとしてCallしないとエラーになる

ブックを起動する際に動く、Workbook_Open の中で他のファイルをActivateするとエラーになる。
その場合は、他のファイルをAcivateする処理の部分を、別のサブルーチンとして作り Call すればエラーにならない。

下の例では、Workbook_Open の中で、K1.xlsm をOpenし、K1.xlsmからデータをCopyしようとしている。
その際、Windows("K1.xlsm").Activate でエラーになる。

Private Sub Workbook_Open()
    
    Dim myFile As String
    Dim myPath As String
    
    myFile = Application.ThisWorkbook.Name
    myPath = Application.ThisWorkbook.Path
    
    ChDir myPath
    Workbooks.Open Filename:=myPath & "\" & "K1.xlsm"
    
    Windows(myFile).Activate

( 省略 )

    '----- ↓ここでエラーになる -----

    Windows("K1.xlsm").Activate
    Sheets("Sheet1").Select
    Range("A285:B500").Select
    Selection.Copy

    Windows(myFile).Activate
    Sheets("Sheet1").Select
    Range("A285").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

( 省略 )

End Sub



そこで、下のように、エラーとなる部分をサブルーチンとして Call することでエラーを回避できる

Private Sub Workbook_Open()
    
    Dim myFile As String
    Dim myPath As String
    
    myFile = Application.ThisWorkbook.Name
    myPath = Application.ThisWorkbook.Path
    
    ChDir myPath
    Workbooks.Open Filename:=myPath & "\" & "K1.xlsm"
    
    Windows(myFile).Activate

( 省略 )

    call MacroK1

( 省略 )

End Sub

Sub MacroK1()
    Dim myFile As String
    
    myFile = Application.ThisWorkbook.Name
    
    Windows("K1.xlsm").Activate
    Sheets("Sheet1").Select
    Range("A285:B500").Select
    Selection.Copy
    Windows(myFile).Activate
    Sheets("Sheet1").Select
    Range("A285").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
End Sub

 

Excel VBA教室 3 VBA(Visual Basic for Applications)とは

bizvba_160Excelには、作業を自動化するためのツール、マクロがあります。

このマクロは、VBA というプログラム言語で記述されています。

VBA(Visual Basic for Applications)は、Officeアプリケーション用のプログラム言語で、Visual Basic の文法を引き継いでいます。


マクロは、同じ作業を繰り返す場合や、複数の操作をまとめて実行するのに非常に便利!!

正しい作業をマクロにしてしまえば、ミスもなくなりますし、操作を人に任すことも可能になります。

マクロはボタンで実行することもできますし、ブックを開いた時、特定のセルを選択した時など、操作に応じて実行することも可能です。



Excel VBA教室 2 マクロの設定 セキュリティ センター

bizvba_160Excel VBAを利用するには、マクロのセキュリティを設定しておきます。

ここでは、一番簡単な方法で説明します。


まずは、Excel 上部の「開発」タブを選択します。
「開発」タブが表示されていない場合は表示させてください。 




k2


「開発」タブの「コード」グループから、「マクロのセキュリティ」を選択します。

「セキュリティ センター」ダイアログボックスが表示されます。

「マクロの設定」で、「警告を表示してすべてのマクロを無効にする」を選択状態にして、「OK」ボタンを押します。


この方法が利用できない場合は、「Excel のオプション」からもセキュリティ センター(セキュリティ レベル)の設定が可能です。




 

Excel VBA教室 1 Excelの設定 開発タブを表示

bizvba_160

まず、Excel VBA を便利に利用する為に、Excel に、「開発」タブを追加しよう。
既に表示されている人は、ここは読み飛ばしてOK!






k1

① Excelの「ファイル」タブを選択
  左のメニューの一番下の「オプション」を選択 (一番下じゃない場合もあるかも?)
  「Excel のオプション」ダイアログボックスが表示されます

② 「Excel のオプション」ダイアログボックスの左のメニューから、「リボンのユーザー設定」を選択

③ 右の「リボンのユーザー設定」で、メインタブにある、「開発」にチェックを入れる

④「開発」 が追加された

この「開発」タブは、あると便利なので表示しておきましょう。




Excel VBA 教室

vba教室

bizvba_160
Excel VBA を使えるようになると、Excelの作業効率を上げることができます。

VBAを一から全て学ぶのではなく、必要な事だけ学べば良いと思う。

Excel VBA を使うことは、手段であり、目的じゃないから。

一緒に Excel VBA を学ぼう!



3 VBA(Visual Basic for Applications)とは
4 複数のマクロを続けて実行する(マクロを組み合わせて一つのマクロを作る)




 

 

Excel-VBA 入力セル間をEnterキーで移動したい。 セルのロックの解除 シートの保護「ロックされていないセル範囲の選択」 Selection.Locked = True ActiveSheet.EnableSelection = xlUnlockedCells

入力セル間をEnterキーで移動させたいことがあります。
入力箇所のみ選択ができ、その他セルは選択ができないようにします。

方法は、
特定のセルのみ、そのセルのロックを解除して、シートの保護をします。
シートの保護をする際、「ロックされていないセル範囲の選択」のチェックボックスにチェックを入れます。


■セルのロックの解除

ロックを解除したいセルの上で右クリックして、 サブメニューから「セルの書式設定(F)...」を選択。
表示された 「セルの書式設定」ダイアログボックスで、「保護」タブを選択。
「ロック」のチェックボックスのチェックを外す。

vba1


VBAでロックの解除を書く場合の例

    Range("F28").Select
    Selection.Locked = False

ロックする時は、FalseをTrueに。


■シートの保護で、「ロックされていないセル範囲の選択」のチェックボックスにチェックを入れる。

vba2

VBAで書く場合は

    ActiveSheet.Protect
    ActiveSheet.EnableSelection = xlUnlockedCells


これだけです。
Enterキーを押せば、次の入力セルに移動できます。


 

Excel-VBA ある文字より後ろの文字を全て取り出す InStr Mid vbTextCompare

「:」より後ろの文字を取り出す。 「:」は全角でも半角でもよし。

Sub test()

    Dim myText As String
    Dim myLong As Long
    
    '「:」は全角でも半角でも大丈夫だし、何文字目にあっても良い
    myText = "売掛金:ABC工業"
    
    
    ' myTxet(売掛金:ABC工業)の 1文字目から 「:」をテキストモード(vbTextCompare)で
    '検索して左から何文字目にあったかを返す
    myLong = InStr(1, myText, ":", vbTextCompare)
    
    '検索文字があれば(0文字目じゃなかったら)、Mid関数で検索文字の次以降を取り出す
    If Not (myLong = 0) Then
        msgbox Mid(myText, myLong + 1)
    End If

End Sub


 


記事検索
Excel VBA ノートについて(注意事項)
このExcel VBA ノートは、Hide Tamura の個人的なVBAノートです。プログラムを再利用したり、コピペで入力の手間を省く為に作ったネット上のノートです。 その為、詳しい解説は書いておりません。エラー等のトラブルには責任は負いません。利用者の環境に合わせて書き換えてご利用ください。
ExcelVBA教室
Excel VBA 担当 Hide Tamura

Excel95? マクロシートがあった頃からExcelVBAを独学で利用しています。現在は、様々な企業様からVBAによる業務効率UPの為のお仕事を頂いております。既にお使いになっているファイルにVBAを利用することで、作業が楽になったり、時間も大幅に短縮されたなど、大変喜ばれています。

■VBA Expert
VBA Expert Standard Crown
ExcelVBA Standard(Odyssey)
Access VBA Standard(Odyssey)
Excel2002 VBA Standard(日本VBA協会)
■MICROSOFT OFFICE USER SPECIALIST
Microsoft Excel version2002 Expert
Microsoft Excel version2002


最新記事
Excelで作る経営計画
Excelで利益計画を立てましょう!会社にいくらの利益が必要で、その為の売上高は?

Excelで作る経営計画
Excel 関数 ノート
Excel関数を中心に、Excelの便利機能や、意外としらない使い方など書いていきます。

Excel 関数 ノート
Access VBAノート
Hide Tamura の個人的なVBAノートです。

頻繁に使うVBAコードなどを記録しコピペして使う為に作りました。お役に立てるようでしたら、お使いください。

Access VBAノート
アクセスカウンター

    • ライブドアブログ