ChatGPTでコードを下書きさせているプログラマーさんたくさんいると思いますが、ちょっとしたコードならすぐ書いてくれるので便利ですよね。
今回のお題は「ExcelのVBAモジュールをファイルに書き出してくれ」です。
じゃあ、やってみましょう。
You
write Excel VBA code to export all the modules
ChatGPT
Sub ExportModules()
Dim wb As Workbook
Dim VBComp As Object
Dim i As Integer
' Reference to the active workbook
Set wb = ThisWorkbook
' Loop through each module in the workbook
For Each VBComp In wb.VBProject.VBComponents
' Check if the component is a code module
If VBComp.Type = 1 Then
' Export the module
ExportModule VBComp, wb.Path & "\" & VBComp.Name & ".txt"
End If
Next VBComp
End Sub
Sub ExportModule(ByRef VBComp As Object, ByVal FilePath As String)
Dim fs As Object
Dim strm As Object
' Create a file system object
Set fs = CreateObject("Scripting.FileSystemObject")
' Create a text stream
Set strm = fs.CreateTextFile(FilePath, True)
' Write the module code to the text file
strm.Write VBComp.CodeModule.Lines(1, VBComp.CodeModule.CountOfLines)
' Close the text stream
strm.Close
' Release objects
Set fs = Nothing
Set strm = Nothing
End Sub
----------------------
もし、現役でこんなコード書いてるやつがいたらグーパンですね。ダメな箇所は赤くしておきました。そもそもLate binding使うのはダメです。こんなのランタイムエラーの温床です。オールランダーのVariantでどれだけ嫌な思いをしてきたか初期のVBプログラマーなら分かると思います。
んで、次の指示はこんな感じ。
You
write Excel VBA code to export all the modules. But do not use "Object".
ChatGPT
Sub ExportModules()
Dim wb As Workbook
Dim VBComp As VBComponent
Dim i As Integer
' Reference to the active workbook
Set wb = ThisWorkbook
' Loop through each module in the workbook
For Each VBComp In wb.VBProject.VBComponents
' Check if the component is a code module
If VBComp.Type = 1 Then
' Export the module
ExportModule VBComp, wb.Path & "\" & VBComp.Name & ".txt"
End If
Next VBComp
End Sub
Sub ExportModule(ByRef VBComp As VBComponent, ByVal FilePath As String)
Dim fs
Dim strm
' Create a file system object
Set fs = CreateObject("Scripting.FileSystemObject")
' Create a text stream
Set strm = fs.CreateTextFile(FilePath, True)
' Write the module code to the text file
strm.Write VBComp.CodeModule.Lines(1, VBComp.CodeModule.CountOfLines)
' Close the text stream
strm.Close
' Release objects
Set fs = Nothing
Set strm = Nothing
End Sub
----------------------
"Object" は使うなと言ったら、As Objectをはずしよったｗｗｗ これ同じことでしょ。んで、今回もAs Integerとか平気で使ってますよね。マシンワードのLong使えよって話です。
3回目のお題はこれ。
You
write Excel VBA code to export all the modules. But do not use CreateObject. use an explicit class type
注釈：CreateObjectは使わないで明示的にクラスタイプを宣言しろ。です
ChatGPT
Sub ExportModules()
Dim wb As Workbook
Dim VBComp As VBComponent
' Reference to the active workbook
Set wb = ThisWorkbook
' Loop through each module in the workbook
For Each VBComp In wb.VBProject.VBComponents
' Check if the component is a code module
If VBComp.Type = 1 Then
' Export the module
ExportModule VBComp, wb.Path & "\" & VBComp.Name & ".txt"
End If
Next VBComp
End Sub
Sub ExportModule(ByRef VBComp As VBComponent, ByVal FilePath As String)
Dim fs As Scripting.FileSystemObject
Dim strm As Scripting.TextStream
' Create a file system object
Set fs = New Scripting.FileSystemObject
' Create a text stream
Set strm = fs.CreateTextFile(FilePath, True)
' Write the module code to the text file
strm.Write VBComp.CodeModule.Lines(1, VBComp.CodeModule.CountOfLines)
' Close the text stream
strm.Close
' Release objects
Set fs = Nothing
Set strm = Nothing
End Sub
おお、いいんじゃない？ Dim i As Integerは宣言したけど、使ってないから消しときました。だってよ。まあいっか。このコードテストするまでもなく動くのは分かりますよね。
最後に、テキストに落としたモジュールをインポートするコード書いてほしいわけです。
You
Write a VBA code to import modules from text files
ChatGPT
Sub ImportModules()
Dim wb As Workbook
Dim fs As Object
Dim f As Object
Dim txtFileName As String
Dim moduleCode As String
' Reference to the active workbook
Set wb = ThisWorkbook
' Create a file system object
Set fs = CreateObject("Scripting.FileSystemObject")
' Loop through each text file in the directory
For Each f In fs.GetFolder(wb.Path).Files
If LCase(Right(f.Name, 4)) = ".txt" Then ' Check if the file is a text file
' Read the module code from the text file
txtFileName = wb.Path & "\" & f.Name
moduleCode = ReadTextFile(txtFileName)
' Import the module into the workbook
ImportModule wb, f.Name, moduleCode
End If
Next f
' Release objects
Set fs = Nothing
Set f = Nothing
End Sub
Function ReadTextFile(ByVal filePath As String) As String
Dim fs As Object
Dim ts As Object
Dim text As String
' Create a file system object
Set fs = CreateObject("Scripting.FileSystemObject")
' Open the text file
Set ts = fs.OpenTextFile(filePath, 1, False)
' Read the content of the text file
text = ts.ReadAll
' Close the text stream
ts.Close
' Release objects
Set ts = Nothing
Set fs = Nothing
' Return the content of the text file
ReadTextFile = text
End Function
Sub ImportModule(ByVal wb As Workbook, ByVal moduleName As String, ByVal moduleCode As String)
Dim newModule As Object
' Add a new module to the workbook
Set newModule = wb.VBProject.VBComponents.Add(1)
' Set the module name
newModule.Name = Left(moduleName, Len(moduleName) - 4)
' Set the module code
newModule.CodeModule.AddFromString moduleCode
End Sub
こ・・・・・こいつ。バカの一つ覚えみたいにCreateObject使いやがって！ クソかよ
これラストチャンスだぞ。ちゃんと書けよ？
You
Write a VBA code to import modules from text files. But do not use CreateObject
２つの回答出してきて、両方ともCreateObject使ってない書き方だけど、どちらが好みですか？ だってさｗｗｗｗ 生意気に ２つのコードを左右に表示してるから許してやる。
Response1
Sub ImportModules()
Dim wb As Workbook
Dim fs As Scripting.FileSystemObject
Dim f As Scripting.File
Dim txtFileName As String
Dim moduleCode As String
' Reference to the active workbook
Set wb = ThisWorkbook
' Create a file system object
Set fs = New Scripting.FileSystemObject
' Loop through each text file in the directory
For Each f In fs.GetFolder(wb.Path).Files
If LCase(Right(f.Name, 4)) = ".txt" Then ' Check if the file is a text file
' Read the module code from the text file
txtFileName = wb.Path & "\" & f.Name
moduleCode = ReadTextFile(txtFileName)
' Import the module into the workbook
ImportModule wb, f.Name, moduleCode
End If
Next f
' Release objects
Set fs = Nothing
Set f = Nothing
End Sub
Function ReadTextFile(ByVal filePath As String) As String
Dim ts As Scripting.TextStream
Dim text As String
' Create a text stream
Set ts = OpenTextFile(filePath)
' Read the content of the text file
text = ts.ReadAll
' Close the text stream
ts.Close
' Release object
Set ts = Nothing
' Return the content of the text file
ReadTextFile = text
End Function
Function OpenTextFile(ByVal filePath As String) As Scripting.TextStream
Dim fs As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
' Create a file system object
Set fs = New Scripting.FileSystemObject
' Open the text file
Set ts = fs.OpenTextFile(filePath, 1, False)
' Release object
Set fs = Nothing
' Return the text stream
Set OpenTextFile = ts
End Function
Sub ImportModule(ByVal wb As Workbook, ByVal moduleName As String, ByVal moduleCode As String)
Dim newModule As Object
' Add a new module to the workbook
Set newModule = wb.VBProject.VBComponents.Add(1)
' Set the module name
newModule.Name = Left(moduleName, Len(moduleName) - 4)
' Set the module code
newModule.CodeModule.AddFromString moduleCode
End Sub
Response2
Sub ImportModules()
Dim wb As Workbook
Dim fs As Object
Dim f As Object
Dim txtFileName As String
Dim moduleCode As String
' Reference to the active workbook
Set wb = ThisWorkbook
' Create a file system object
Set fs = New Scripting.FileSystemObject
' Loop through each text file in the directory
For Each f In fs.GetFolder(wb.Path).Files
If LCase(Right(f.Name, 4)) = ".txt" Then ' Check if the file is a text file
' Read the module code from the text file
txtFileName = wb.Path & "\" & f.Name
moduleCode = ReadTextFile(txtFileName)
' Import the module into the workbook
ImportModule wb, f.Name, moduleCode
End If
Next f
' Release objects
Set fs = Nothing
Set f = Nothing
End Sub
Function ReadTextFile(ByVal filePath As String) As String
Dim fs As Object
Dim ts As Object
Dim text As String
' Create a file system object
Set fs = New Scripting.FileSystemObject
' Open the text file
Set ts = fs.OpenTextFile(filePath, 1, False)
' Read the content of the text file
text = ts.ReadAll
' Close the text stream
ts.Close
' Release objects
Set ts = Nothing
Set fs = Nothing
' Return the content of the text file
ReadTextFile = text
End Function
Sub ImportModule(ByVal wb As Workbook, ByVal moduleName As String, ByVal moduleCode As String)
Dim newModule As Object
' Add a new module to the workbook
Set newModule = wb.VBProject.VBComponents.Add(1)
' Set the module name
newModule.Name = Left(moduleName, Len(moduleName) - 4)
' Set the module code
newModule.CodeModule.AddFromString moduleCode
End Sub
２つの違いはOpenTextFileという関数があるかないかだけだね。別にたいした違いではないけど、Response1の方がよさげかな。
こんなコード書いてる部下がいたら再教育してあげてください。アホ丸出しですね。
Dim xCell As Excel.Range
Set xCell = ActiveCell
Dim xSheet As Excel.Worksheet
Set xSheet = ActiveSheet
Set xCell = xSheet.Range("C1:C10")