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をはずしよったwww これ同じことでしょ。んで、今回も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
2つの回答出してきて、両方ともCreateObject使ってない書き方だけど、どちらが好みですか? だってさwwww 生意気に 2つのコードを左右に表示してるから許してやる。
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
2つの違いはOpenTextFileという関数があるかないかだけだね。別にたいした違いではないけど、Response1の方がよさげかな。
こんなコード書いてる部下がいたら再教育してあげてください。アホ丸出しですね。
Dim xCell As Excel.Range
Set xCell = ActiveCell
Dim xSheet As Excel.Worksheet
Set xSheet = ActiveSheet
Set xCell = xSheet.Range("C1:C10")