Excel VBAプログラマがChatGPTをこのように活用しています（備忘録）

実際に業務で必要になったときに、ChatGPTに作ってもらったプログラムを追加していきます。
ちょっとした関数を作ってもらうにはとても便利で、ググってここのページにたどり着いてコピペするより、ChatGPTにこんな感じでプログラム作ってもらってるんだという事が伝われば幸いです。

ChatGPT Plusには課金しているのですが、応答速度と回数制限の関係でGPT 3.5を使用しています。

excel vbaでファイルの存在チェックをするには？
1. FileSystemObjectでエラーが発生します
文字列を渡すとhtmlタグを除去する関数作って
文字列を渡すとURLを除去する関数を作って
1. http:// にも対応して
◆から◆で囲まれた文字を除去する関数作って
32bit用に宣言されたAPI定義を64bit版に対応させて
改行なしカンマ区切りの文字列がある。文字列を渡したらn番目までの文字列を返す関数を作って
1. 上記の例だと、 “apple,banana,orange” までを返す関数を作って
2. excel vbaではJoin$は第２引数までしかありません。

excel vbaでファイルの存在チェックをするには？

Excel VBAでは、ファイルの存在を確認するために、FileSystemObjectオブジェクトを使用することができます。以下は、ファイルの存在を確認するためのVBAコード例です。

Dim fso As New FileSystemObject
Dim filePath As String

filePath = "C:\example\file.xlsx"

If fso.FileExists(filePath) Then
    'ファイルが存在する場合の処理
Else
    'ファイルが存在しない場合の処理
End If

このコードでは、FileSystemObjectオブジェクトを使用して、指定されたファイルパスのファイルが存在するかどうかを確認します。ファイルが存在する場合は、If文のTrueブロック内の処理が実行され、ファイルが存在しない場合は、Falseブロック内の処理が実行されます。

FileSystemObjectでエラーが発生します

FileSystemObjectを使用すると、コンピュータ上のファイルおよびフォルダにアクセスできますが、正しい方法で使用されていない場合、エラーが発生する可能性があります。以下は、FileSystemObjectでエラーが発生する原因と、解決策の例です。

ライブラリが参照されていない

FileSystemObjectを使用するには、Microsoft Scripting Runtimeライブラリを参照する必要があります。Visual Basic Editorで、[ツール]メニューから[参照設定]をクリックし、[Microsoft Scripting Runtime]を選択してください。

ファイルが見つからない

FileSystemObjectでファイルを操作する場合、ファイルが見つからない場合にエラーが発生することがあります。ファイルが存在することを確認し、ファイルパスが正しいことを確認してください。

ファイルにアクセスできない

FileSystemObjectでファイルを操作する場合、ファイルが別のアプリケーションによって開かれている場合、アクセスが拒否されることがあります。ファイルを閉じていることを確認して、再度操作を試みてください。

例えば、以下はファイルが存在しない場合にエラーが発生しないようにするためのコード例です。

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Dim filePath As String
filePath = "C:\example\file.xlsx"

If fso.FileExists(filePath) Then
    'ファイルが存在する場合の処理
Else
    'ファイルが存在しない場合の処理
    MsgBox "ファイルが見つかりませんでした。", vbCritical
End If

Set fso = Nothing

このコードでは、FileExistsメソッドがTrueを返す場合にファイルが存在すると判断されます。Falseを返す場合は、メッセージボックスでファイルが見つからなかった旨を通知します。また、FileSystemObjectオブジェクトは、使用後に解放する必要があります。

文字列を渡すとhtmlタグを除去する関数作って

以下は、VBAで文字列からHTMLタグを除去する関数の例です。この関数は、正規表現を使用してHTMLタグを検索し、置換します。

Function RemoveHTMLTags(str As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "<[^>]+>"
    regex.Global = True
    RemoveHTMLTags = regex.Replace(str, "")
End Function

この関数は、VBScript.RegExpオブジェクトを使用して、HTMLタグを表すパターンを検索します。パターン<[^>]+>は、<で始まり、>で終わる任意の文字列をマッチングします。Globalプロパティは、マッチング対象の文字列全体を検索するように設定されています。Replaceメソッドは、HTMLタグを空の文字列に置換して、HTMLタグを除去した文字列を返します。

使用例：

Sub TestRemoveHTMLTags()
    Dim str As String
    str = "<p>これはHTMLタグの<b>テスト</b>です。</p>"
    Debug.Print RemoveHTMLTags(str)
End Sub

このコードでは、<p>と</p>で囲まれた文字列を含む変数strを定義し、RemoveHTMLTags関数に渡しています。Debug.Print文で、HTMLタグが除去された文字列が出力されます。

文字列を渡すとURLを除去する関数を作って

以下は、VBAで文字列からURLを除去する関数の例です。この関数は、正規表現を使用してURLを検索し、置換します。

Function RemoveURL(str As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "https?:\/\/[^\s]+"
    regex.Global = True
    RemoveURL = regex.Replace(str, "")
End Function

この関数は、VBScript.RegExpオブジェクトを使用して、URLを表すパターンを検索します。パターンhttps?:\/\/[^\s]+は、httpまたはhttpsで始まり、スペースで終わるまでの任意の文字列をマッチングします。Globalプロパティは、マッチング対象の文字列全体を検索するように設定されています。Replaceメソッドは、URLを空の文字列に置換して、URLを除去した文字列を返します。

使用例：

Sub TestRemoveURL()
    Dim str As String
    str = "この文書には、https://www.example.com/test.html というURLが含まれています。"
    Debug.Print RemoveURL(str)
End Sub

このコードでは、https://www.example.com/test.htmlというURLを含む変数strを定義し、RemoveURL関数に渡しています。Debug.Print文で、URLが除去された文字列が出力されます。

http:// にも対応して

前回の回答で提供した関数を改良し、http://とhttps://の両方に対応するように変更したコードを以下に示します。

Function RemoveURL(str As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "https?:\/\/[^\s]+"
    regex.Global = True
    RemoveURL = regex.Replace(str, "")

    regex.Pattern = "http?:\/\/[^\s]+"
    RemoveURL = regex.Replace(RemoveURL, "")
End Function

この関数では、まずhttps://に対応するために正規表現パターンをhttps?:\/\/[^\s]+に設定して、URLを検索します。その後、http://に対応するために、同様の処理をhttp?:\/\/[^\s]+のパターンで実施します。Replaceメソッドにより、URLが空の文字列に置換され、URLを除去した文字列が返されます。

使用例：

Sub TestRemoveURL()
    Dim str As String
    str = "この文書には、http://www.example.com/test.html と https://www.example.com/test.html というURLが含まれています。"
    Debug.Print RemoveURL(str)
End Sub

このコードでは、http://www.example.com/test.htmlとhttps://www.example.com/test.htmlという2つのURLを含む変数strを定義し、RemoveURL関数に渡しています。Debug.Print文で、両方のURLが除去された文字列が出力されます。

◆から◆で囲まれた文字を除去する関数作って

アプリをインストールして通知設定をしておけば◆aaa◆で成果発生を知ることができます。
◆bbbb◆
・cccc
上記の文字列から、◆から◆で囲まれた文字を除去する関数作って

以下は、VBAで文字列から指定された区間を除去する関数の例です。この関数は、正規表現を使用して指定された区間を検索し、置換します。

Function RemoveEnclosedString(str As String, enclosedString As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = enclosedString
    regex.Global = True
    RemoveEnclosedString = regex.Replace(str, "")
End Function

この関数は、VBScript.RegExpオブジェクトを使用して、指定された区間を表すパターンを検索します。パターンは、引数enclosedStringの文字列そのままを設定しています。Globalプロパティは、マッチング対象の文字列全体を検索するように設定されています。Replaceメソッドは、指定された区間を空の文字列に置換して、指定された区間を除去した文字列を返します。

使用例：

Sub TestRemoveEnclosedString()
    Dim str As String
    str = "アプリをインストールして通知設定をしておけば◆aaa◆で成果発生を知ることができます。◆bbb◆・ccc"
    Debug.Print RemoveEnclosedString(str, "◆.+?◆")
End Sub

このコードでは、◆aaa◆と◆bbb◆という2つの区間を含む変数strを定義し、RemoveEnclosedString関数に渡しています。第2引数には、除去する区間を表す文字列"◆.+?◆"を設定しています。Debug.Print文で、指定された区間が除去された文字列が出力されます。

32bit用に宣言されたAPI定義を64bit版に対応させて

以下の宣言を64bit版用に修正して

Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, _
ByVal dwMilliseconds As Long) As Long
Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

以下は、64ビット版に対応したVBAコード例です。

#If VBA7 Then
    Declare PtrSafe Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As LongPtr, _
                                        ByVal dwMilliseconds As Long) As LongPtr
    Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, _
                                            ByVal bInheritHandle As LongPtr, _
                                            ByVal dwProcessId As LongPtr) As LongPtr
    Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As LongPtr
#Else
    Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, _
                                     ByVal dwMilliseconds As Long) As Long
    Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, _
                                             ByVal bInheritHandle As Long, _
                                             ByVal dwProcessId As Long) As Long
    Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
#End If

64ビット版の場合、LongPtr型を使用して、32ビット版と同様にハンドルを扱います。LongPtr型は、32ビットまたは64ビットの環境に依存しない整数型で、64ビット環境では8バイトの長さを持ちます。また、PtrSafeキーワードを使用して、ポインタに対するセキュリティチェックを有効にします。PtrSafeキーワードは、64ビット版VBAで使用されます。

上記の修正を適用することで、64ビット版のVBAでコンパイルできるようになります。

改行なしカンマ区切りの文字列がある。文字列を渡したらn番目までの文字列を返す関数を作って

以下は、改行なしカンマ区切りの文字列からn番目までの文字列を取得する関数の例です。

Function GetNthString(str As String, n As Long) As String
    Dim arr As Variant
    arr = Split(str, ",")
    If n > UBound(arr) + 1 Then
        GetNthString = ""
    Else
        GetNthString = arr(n - 1)
    End If
End Function

この関数では、Split関数を使用して、カンマで区切られた文字列を配列に変換します。nが配列の範囲を超える場合、空の文字列を返します。それ以外の場合、指定されたインデックスの要素を返します。n - 1としているのは、配列のインデックスは0から始まるためです。

使用例：

Sub TestGetNthString()
    Dim str As String
    str = "apple,banana,orange,grape,peach"
    Debug.Print GetNthString(str, 3)
End Sub

このコードでは、改行なしカンマ区切りの文字列を含む変数strを定義し、GetNthString関数に渡しています。第2引数には、取得したい要素のインデックス番号3を設定しています。Debug.Print文で、指定されたインデックスの要素であるorangeが出力されます。

上記の例だと、 “apple,banana,orange” までを返す関数を作って

以下は、改行なしカンマ区切りの文字列からn番目までの文字列を取得する関数の例です。

Function GetNStrings(str As String, n As Long) As String
    Dim arr As Variant
    arr = Split(str, ",")
    If n > UBound(arr) + 1 Then
        GetNStrings = ""
    Else
        GetNStrings = Join$(arr, ",", 1, n)
    End If
End Function

この関数では、Split関数を使用して、カンマで区切られた文字列を配列に変換します。nが配列の範囲を超える場合、空の文字列を返します。それ以外の場合、Join関数を使用して、最初のn個の要素をカンマで区切った文字列を作成して返します。

使用例：

Sub TestGetNStrings()
    Dim str As String
    str = "apple,banana,orange,grape,peach"
    Debug.Print GetNStrings(str, 3)
End Sub

このコードでは、改行なしカンマ区切りの文字列を含む変数strを定義し、GetNStrings関数に渡しています。第2引数には、取得したい要素の数3を設定しています。Debug.Print文で、指定された要素の範囲であるapple,banana,orangeが出力されます。

excel vbaではJoin$は第２引数までしかありません。

申し訳ありません。私の回答に誤りがありました。VBAのJoin$関数は、第3引数を持たないため、指定した方法では機能しません。

以下は、正しい関数の例です。

Function GetNStrings(str As String, n As Long) As String
    Dim arr As Variant
    arr = Split(str, ",")
    If n > UBound(arr) + 1 Then
        GetNStrings = ""
    Else
        ReDim Preserve arr(n - 1)
        GetNStrings = Join(arr, ",")
    End If
End Function

この関数では、Split関数を使用して、カンマで区切られた文字列を配列に変換します。nが配列の範囲を超える場合、空の文字列を返します。それ以外の場合、配列をn個の要素にリサイズして、Join関数を使用して、カンマで区切られた文字列を作成して返します。

使用例：

Sub TestGetNStrings()
    Dim str As String
    str = "apple,banana,orange,grape,peach"
    Debug.Print GetNStrings(str, 3)
End Sub