神エクセルにさよなら

VBA入門

ショートカット・キー

キー	内容
Alt + L	開発リボンに切り替える
Alt + F11	VBAエディタを開く

予備知識

開発リボンを表示させる

当初はExcelやWordのリボンには開発というものはない. ファイル-> オプションからリボンのユーザー設定で開発にチェックを付けておく必要があります.

xlsx vs xlsm

拡張子	マクロ/VBAの有無
xlsx	無
xlsm	有

VBAの開発をするときは, エクセルでスプレッドシートを保存するときに拡張子をxlsmに変更しておきます.

マクロ vs VBA

拡張子	意味
マクロ	マクロはエクセルで実行された操作をまとめたもの
VBA (Visual Basic for Application)	制御構文などを含みより高度な処理をプログラミングにより実現する

オブジェクト

オブジェクト名	説明
Application	ExcelとかWordといったアプリケーションそのもの
Workbook	Excelで開いている作業中のファイル
Workbooks	Excelで開いている作業中のファイル全部
ThisWorkbook	VBAをもつワークブックのこと
ActiveWorkbook	作業中のExcelファイルで一番上に表示されているもの
Worksheet	スプレッドシートを表す
Worksheets	ワークブックに含まれるスプレッドシードのコレクションのこと
ActiveSheet	スプレッドシートで一番上に表示されている(フォーカスされている)もの
Selection	ActiveWorksheet現在選択中のセルを表す
Document	Wordのファイルのこと
ActiveDocument	フォーカスされているドキュメントのこと
ThisDocument	VBAが実行されるドキュメントのこと

モジュール

Hello World!

' このマクロはHello Worldメッセージを表示するためのサブルーチンです
Sub HelloWorld()
    '画面にメッセージボックスがHello World!と表示される'
    MsgBox "Hello World!"
End Sub

基本的な構文

Subルーチン

Sub
    'ここに処理を書いていく'
End Sub

変数定義

Sub
    Dim i As Integer 'ループ変数'
End Sub

ループ処理

Sub WriteValuesToColumn()
    Dim i As Integer
    Dim rowCount As Integer
    
    rowCount = 10 ' 列のレコード数を設定します
    
    For i = 1 To rowCount
        ' 列Aの各セルにループ変数の値を書き込みます
        Cells(i, 1).Value = i
    Next i
End Sub

条件分岐

スプレッドシートのA1セルの値に応じて分岐する処理は以下のようになります.

Sub ProcessBasedOnInput()
    Dim value As Boolean
    
    ' セルの値を取得
    value = Range("A1").Value
    
    ' 分岐処理
    If value = True Then
        ' Trueの場合の処理
        MsgBox "Trueが選択されました。"
    Else
        ' Falseの場合の処理
        MsgBox "Falseが選択されました。"
    End If
End Sub

データへのアクセス

ExcelとWordの相互運用

オブジェクト・ライブラリの参照

まずVisual Basic Editorでツールから参照設定で

Microsoft Word 1.5 Object Library
Microsoft Excel 1.5 Object Library

を指定する必要がある. そうすることでWord.Applicationといった型が使えるようになります.

Office アプリケーションを別のアプリケーションから制御する

Applicationオブジェクト

アプリケーションの起動をする場合にまず

Dim excelApp As Excel.Application
Dim wordApp As Word.Application

ここに取得したインスタンスを格納する変数を定義します.

アプリケーション・インスタンスの取得

WordやExcelのアプリケーションが起動している場合GetObject関数によってインスタンスを取得できる. ただしアプリケーションの実態が存在しない場合実行時エラーとなるので, これを上手く処理して必要な場合アプリケーションをVBAから起動する必要があります.

GetObject functionのリファレンスには

Dim excelApp As Excel.Application

On Error Resume Next  ' Defer error trapping.
' Getobject function called without the first argument returns a 
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set excelApp = Getobject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear  

Comparison Operators in Visual Basic

IsError (GetObject(, "word.application")) is returning an error when word is not running instead of returning trueを参考ンして書くと以下のようになります.

この場合なければアプリケーション・インスタンスを新規に作成しています.

Dim wordApp As Object

'~~> Establish an Word application object
On Error Resume Next
Set wordApp = GetObject(, "Word.Application")

If Err.Number <> 0 Then 'エラーがゼロでない(つまり何らかのエラーがあった場合)
    Set wordApp = CreateObject("Word.Application")
End If
Err.Clear
On Error GoTo 0 '無効にしたエラー処理を有効戻す

これでアプリの起動時にはそのインスタンスを取得し, そうでない場合はインスタンスを作成し起動するという処理の流れができました.

On Error Resume NextからOn Error GoTo 0までがワンセットです. On Error Resume Nextというハンドリング文で有効にしたエラー処理を, On Error GoTo 0で再度無効にしています. つまりOn Error GoTo 0以降はまた実行時エラーで処理は止まるようになります.

これは以下の文章を根拠にしています.

On Error statementによると,

An "enabled" error handler is one that is turned on by an On Error statement;

とあるので, On-Error文でエラー・ハンドラー有効になると解釈できます.

Microsoft Excel が既に起動されているかどうかを調べる（その１）

アクティブなワークシートの取得

Dim activeSheet As WorkSheet
Set activeSheet = ThisWorkbook.ActiveSheet

ThisWorkbookはthisのようなもので, 基本的には省略可能なようです.

連想配列

辞書型とか連想配列型というのはないようですが, Objectが連想配列として作動するようです.

Sub DictionaryExample()
     Dim dict As Object
     Set dict = CreateObject("Scripting.Dictionary")
     
     dict.Add "キー1", "値1"
     dict.Add "キー2", "値2"
     dict.Add "キー3", "値3"
End Sub

連想配列の値に配列を指定することもできます.

Sub DictionaryWithArrayValue()
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim arr() As Variant
    arr = Array("Apple", "Angle", "Art")
    
    dict.Add "A", arr
    
    ' 配列の値にアクセスして表示
    Dim valueArray() As Variant
    valueArray = dict("A")
    
    Dim i As Long
    For i = LBound(valueArray) To UBound(valueArray)
        Debug.Print valueArray(i)
    Next i
    
    Set dict = Nothing
End Sub

使用済みレコードの数を数える

Dim lastRow As Long
Dim recordCount As Long
Dim dataRange As Range

'A列の最終使用行を取得'
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set dataRange = ActiveSheet.Range("A1:A" & lastRow)

recordCount = WorksheetFunction.CountA(dataRange)

ここで&はA1:AとlastRowを連結するための演算子です. おってlastRowが10なら"A1:A10"という文字列になり範囲を表すことになります.

次にWorksheetFunction.CountAメソッドは以下のような特徴があるようです.

CountA メソッドでは、エラー値や空白文字列 ("") を含め、すべてのデータ型の値が計算の対象となります。 ただし、空白セルだけは計算の対象となりません。

このことから仮に空行が含まれている場合は, それらを除いた個数を返してくれます.

ワークブック

ワークブックの取得

ActiveWorkbookオブジェクトを使うと

Dim activeBook As Workbook
Set activeBook = ActiveWorkbook

Dim activeBook As Workbook
Set activeBook = ActiveWorkbook

Dim bookName As String
Dim openedBook As Workbook

bookName = "Workbook.xlsx" ' ブックの名前を指定してください
Set openedBook = Workbooks(bookName)

Dim filePath As String
Dim fileBook As Workbook

filePath = "C:\Path\To\Workbook.xlsx" ' ファイルのパスを指定してください
Set fileBook = Workbooks.Open(filePath)

ワークブックの作成

マスターデータからデータを加工するような場合にデータを新規のシートなどを作成して保存したい場合があります.

Sub CreateNewWorkbook()
    Dim xlApp As Object ' Excel.Applicationオブジェクト用の変数
    Dim xlWB As Object ' Excel.Workbookオブジェクト用の変数
    
    ' Excelアプリケーションを作成し、表示します
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    
    ' 新しいワークブックを作成します
    Set xlWB = xlApp.Workbooks.Add
    
    ' 新規ワークブックの名前を変更します
    xlWB.SaveAs "新しいワークブック名.xlsx" ' 新しいワークブックの名前を指定して保存
    
    ' 必要な処理が完了したら、オブジェクトを解放します
    Set xlWB = Nothing
    Set xlApp = Nothing
End Sub

With構文

チェックボックス

フォームではなくスプレッドシートにチェックボックスを埋め込むのは手間のわりに得られるものが少ないので使わないほうが良いのかもしれません.　あくまでデータを入力すべきでUIはおまけと考えるべきでしょう. フォームモジュールのようなデータ入力用のUI作成のためにあると考えた方がいいともいます.

エクセルのデータの入力規則でリストとして有, 無のような選択肢を用意する方がいいのかもしれません.

チェックボックスの値に応じて処理を分岐させる

チェックボックスのようなものを使うとデータの入力が簡単になります。

Sub ProcessCheckboxData()
    Dim checkboxValue As Boolean
    
    ' チェックボックスコントロールの値を取得します
    checkboxValue = ActiveSheet.CheckBoxes("CheckBox1").Value
    
    ' 取得した真偽値データを処理します
    If checkboxValue = True Then
        ' チェックが入っている場合の処理
        MsgBox "チェックが入っています"
    Else
        ' チェックが入っていない場合の処理
        MsgBox "チェックが入っていません"
    End If
End Sub

前処理

前処理をしておくとVBAが煩雑になるのを防げる.

あかさたなはやらわの行で索引をつける

=MID("アカサタナハマヤラワ",MATCH(CODE(ASC(B2))-177,{0,5,10,15,20,25,30,35,38,43},1),1)&"行"

という関数でできるらしいのだが, 当初意味が分からなかった. ASCIIと半角カナ一覧によると半角のアは177(10)で表現されるようです. つまりアの場合は0になります. 五十音は連続した値となっているいるので, ヤ行以外は5ずつ増やしていくとあかさたなはやらわのいずれに該当するかが分かります.

ひらがなの場合は別途あの文字コードを取得して177と入れ替えると同じ事ができると思われます.

Ｅｘｃｅｌで並び替え後にア行カ行などをつけたい

Reference

Excel & Wordの一般的な使い方

• 【Excel】3種類のドロップダウンリストを使い分けて業務全体の効率を上げる！
• エクセルのチェックボックス機能は非効率。プルダウンで代用すべき3つの理由と作り方を解説

VBA

• Office VBA の基礎知識
• Visual Basic conceptual topics
• Using With statements