VBA

VBAの＆記法が嫌いすぎてPythonのformatメソッドを自作して導入した話

VBAの文字列連結に使う＆が見にくいからPythonのformatメソッドを真似して自作してみました。

VBAの＆記法が嫌いすぎてPythonのformatメソッドを自作して導入した話

コンニチハ、ハルミです。

今回は珍しくVBAの話をしていきます。

何だそれという方に簡単に説明すると、VBAはExcelのマクロを作成するための言語です。

VBAの開発体験がとにかく悪いので、Pythonのformatメソッドを真似して自作してみました。

VBAの開発体験がとにかく悪い

社内転職して、工場の現場職からDX推進部門のエンジニアになって1ヶ月半経ちました。

私の部署では主にVBAとPythonを使ってDXを行っているので、当ブログに使用しているようなTypeScriptやReact、Next.jsなどの技術は使っていません。

私は現在の部署の異動してからVBAやPythonを学習して、今はそれなりに使いこなせるようになりました。

そんな中で、現在とても感じているのは。。。

VBAは色々とコードが書きにくい！！！

こんなこと今更私が言わなくても、世界中のVBAユーザーが思っていることではありますが、
パッと今思いつくだけでも

  • 文字列連結に使う＆が見にくい
  • クラスのコンストラクタに引数を渡せない
  • VBEがどうしようもなく使いにくい
  • 型の柔軟性が低い
  • etc...

などなど、上げだしたらキリがありません。

それに比べて同時期に学習したPythonの柔軟性は素晴らしいです。

なんとかVBAでもPythonのようなコードの書き心地を実現できないか。。。ということで、今回はPythonのformatメソッドを真似して自作してみました。

VBAの文字列連結に使う＆が見にくい

VBAで文字列連結を行う際には、＆を使って行います。

example
Dim str As String
Dim i As Long

str = "Hello" & " World"

For i = 1 To 5
  Me.Range("A" & i).Value = str & " " & i
Next i

' 結果
' Hello World 1     #A1セル
' Hello World 2     #A2セル
' Hello World 3     #A3セル
' Hello World 4     #A4セル
' Hello World 5     #A5セル

まだこれくらいならいいですが、連結する数が増えてくると非常に見にくくなります。

example
Dim result as String

Const name As String = "山田"
Const age As Integer = 20
Const address As String = "東京都"

result = name & "さんは" & age & "歳で、" & address & "に住んでいます。"

Debug.Print result

' 結果
' 山田さんは20歳で、東京都に住んでいます。

３つの変数を文字列連結するだけでも、これだけ見にくくなってしまいます。

VBAと違って、他の言語では大抵プレースホルダーを使ってコードを書けるので、可読性が高いです。

example.py
# Pythonの例
name = "山田"
age = 20
address = "東京都"

result1 = "{}さんは{}歳で、{}に住んでいます。".format(name, age, address)
result2 = f"{name}さんは{age}歳で、{address}に住んでいます。"

print(result1) # 山田さんは20歳で、東京都に住んでいます。
print(result2) # 山田さんは20歳で、東京都に住んでいます。
example.js
// JavaScriptの例
const name = "山田";
const age = 20;
const address = "東京都";

const result = `${name}さんは${age}歳で、${address}に住んでいます。`;

console.log(result); // 山田さんは20歳で、東京都に住んでいます。

プログラミングにおいて可読性は非常に重要です。

今回はVBAでPythonのformatメソッドを真似して自作してみました。

自作したもの

いきなり完成コードを載せますが、少しずつ解説していきます。

今回作成したプログラムは、今後汎用的に使用していきたいので、クラスモジュールで開発してアドイン化していきます。

コードの解説

クラスモジュール
Option Explicit

Private inputString As String

' コンストラクタ
Public Sub Init(ByVal text As String, ParamArray values() As Variant)
    ' ParamArrayは配列として渡される
    inputString = FormatString(text, values)
End Sub


' フォーマット済みの文字列を返すプロパティ
Public Property Get Value() As String
    Value = inputString
End Property


' ロジック: {}を順番に置換
Private Function FormatString(ByVal text As String, ByVal args As Variant) As String
    Dim i As Long
    Dim result As String
    Dim placeholderCount As Long
    
    result = text
    placeholderCount = CountPlaceholders(text)
    
    'プレースホルダ数と引数が一致しない場合にエラー
    If placeholderCount <> (UBound(args) - LBound(args) + 1) Then
        Err.Raise 1001, "FString", "引数の数がプレースホルダ{}の数と一致しません。"
    End If
    
    ' ParamArrayはVariant型配列として扱う
    For i = LBound(args) To UBound(args)
        result = ReplaceOnce(result, "{}", CStr(args(i)))
    Next i
    
    FormatString = result
End Function


' 最初の{}のみ置換する関数
Private Function ReplaceOnce(ByVal text As String, ByVal search As String, ByVal replaceWith As String) As String
    Dim pos As Long
    pos = InStr(1, text, search)
    If pos > 0 Then
        ReplaceOnce = Left(text, pos - 1) & replaceWith & Mid(text, pos + Len(search))
    Else
        ReplaceOnce = text
    End If
End Function


'プレースホルダの数を確認する関数
Private Function CountPlaceholders(ByVal text As String) As Long
    Dim count As Long
    Dim pos As Long
    
    pos = 1
    Do While InStr(pos, text, "{}") > 0
        count = count + 1
        pos = InStr(pos, text, "{}") + 2
    Loop
    
    CountPlaceholders = count
End Function

順に解説していきます。

コンストラクタ

Public Sub Init(ByVal text As String, ParamArray values() As Variant)
    ' ParamArrayは配列として渡される
    inputString = FormatString(text, values)
End Sub

VBAはコンストラクタに引数を渡すことができないので、Initメソッドを作成しています。

第1引数にはフォーマットする文字列を渡し、第2引数以降にはフォーマットする値を渡します。
VBAにはParamArrayというキーワードがあり、これを使うことで可変長引数を受け取ることができます。

これによって第2引数以降の値を全て一つの配列として受け取ることができます。
型にはVariant型を使用しているので、数値や文字列などの違いを意識しなくても大丈夫です。

FormatStringメソッドは、のちほど解説します。

フォーマット済みの文字列を返すプロパティ

Public Property Get Value() As String
    Value = inputString
End Property

Initメソッドでフォーマット済みの文字列をinputStringに格納しているので、このプロパティを呼び出すことでフォーマット済みの文字列を取得することができます。

位置引数として割り当てているので、名前引数は使用できない点に注意が必要です。

ロジック

Private Function FormatString(ByVal text As String, ByVal args As Variant) As String
    Dim i As Long
    Dim result As String
    Dim placeholderCount As Long
    
    result = text
    placeholderCount = CountPlaceholders(text)
    
    'プレースホルダ数と引数が一致しない場合にエラー
    If placeholderCount <> (UBound(args) - LBound(args) + 1) Then
        Err.Raise 1001, "FString", "引数の数がプレースホルダ{}の数と一致しません。"
    End If
    
    ' ParamArrayはVariant型配列として扱う
    For i = LBound(args) To UBound(args)
        result = ReplaceOnce(result, "{}", CStr(args(i)))
    Next i
    
    FormatString = result
End Function

Initメソッドの引数で渡された文字列をFormatStringメソッドでフォーマットしています。

配列内の値を順番に文字列内の{}をReplaceOnceメソッドで置換していきます。

ReplaceOnceメソッドの引数に渡すときにCStr関数を使用しているのは、Variant型配列の値を文字列に変換するためです。

最初の{}のみ置換する関数

Private Function ReplaceOnce(ByVal text As String, ByVal search As String, ByVal replaceWith As String) As String
    Dim pos As Long
    pos = InStr(1, text, search)
    If pos > 0 Then
        ReplaceOnce = Left(text, pos - 1) & replaceWith & Mid(text, pos + Len(search))
    Else
        ReplaceOnce = text
    End If
End Function

ReplaceOnceメソッドは、文字列内の最初の{}のみ置換する関数です。

InStr関数で文字列内の{}の位置を取得し、Left関数とMid関数で置換する文字列を作成しています。

クラスのインスタンスを作成するためのPublicメソッドを作成

このままこのクラスモジュールを使用したいExcelブックにコピペして使っていいのですが、ブックのクラスモジュールに毎回コピペするのは面倒ですよね。

なので、このクラスモジュールをアドイン化して、外部からインポートできるようにします。

そしてここでの注意点は、アドインとしてインポートしたクラスは直接インスタンスを作成できないということです。

なので、標準モジュールの方でクラスのインスタンスを作成するPublicメソッドを作成します。

標準モジュール
Option Explicit

'FStringクラスのインスタンスを生成する関数
Public Function CreateFString() As FString

    Dim f As FString: Set f = New FString
    Set CreateFString = f
    
End Function

これでアドイン化する準備が整いました。
アドイン化の方法まで解説すると記事が長くなるので割愛しますが、以下の記事が参考になりました。

読み込み中...

実際に使ってみる

上記で作成したFStringクラスの使用例を以下に示します。

使用例
Dim f As FString
Set f = CreateFString() ' FStringクラスのインスタンスを生成

Const name As String = "山田"
Const age As Integer = 20
Const address As String = "東京都"

f.Init "{}さんは{}歳で、{}に住んでいます。", name, age, address
Debug.Print f.Value

' 結果
' 山田さんは20歳で、東京都に住んでいます。

このように、VBAでもPythonのformatメソッドのように文字列をフォーマットすることができました。

このクラスを使えば、&を一切使うことなく大量の文字列をフォーマットすることができるようになります。

VBAでDBを扱う際のODBC接続文字列などを作成したり、SQL文を作成したりするときに使えるかもしれません。

ODBC接続文字列を作成する例
Dim f As FString
Set f = CreateFString()

Const DRIVER as String = "{PostgreSQL Unicode(x64)}"
Const SERVER as String = "localhost"
Const PORT as String = "5432"
Const DATABASE as String = "testDB"
Const USER as String = "testUser"
Const PASSWORD as String = "testPassword"

f.Init "Driver={};Server={};Port={};Database={};UID={};PWD={};", DRIVER, SERVER, PORT, DATABASE, USER, PASSWORD
Debug.Print f.Value

' 結果
' Driver={PostgreSQL Unicode(x64)};Server=localhost;Port=5432;Database=testDB;UID=testUser;PWD=testPassword;
SQL文を作成する例
Dim f As FString
Set f = CreateFString()

Const TABLE_NAME As String = "testTable"
Const COLUMN_NAME As String = "testColumn"
Const WHERE_VALUE As String = "testValue"

f.Init "SELECT * FROM {} WHERE {} = {}", TABLE_NAME, COLUMN_NAME, WHERE_VALUE
Debug.Print f.Value

' 結果
' SELECT * FROM testTable WHERE testColumn = testValue

まとめ

VBAでもPythonのformatメソッドのように文字列をフォーマットすることができました。

VBAは色々と書きにくいので、このような自作クラスを作成してみるのも面白いかもしれません。

まだまだVBA初心者なので、自身のVBAスキルも向上していきたいです。

それではまた👋

profile

ハルミ

1997年生まれ。某メーカーの新米DX担当。
三度の飯より効率化が好き。
プログラミングにハマり、Webエンジニアを目指す。
現在React/Next.jsを学習しています🚀