excel - 创建自安装宏？

Question

您好，我为我的同事创建了许多宏。我当前用于分发到另一台计算机的方法是进入 vba 编辑器并导入。

我真的很想为宏制作一种“安装程序”，它允许用户安装新的宏而无需进入编辑器。我不确定这是否可能，但欢迎任何想法!

谢谢!

Answer 1

您需要在引用下启用 Microsoft Scripting Runtime 库。 (VBE -> 工具 -> 引用。选中该框。)

基本上，您创建一个字符串来保存要安装的宏的代码。显然，字符串可能非常长，包含很多行代码，因此您可能需要多个字符串变量。

Dim toF As Workbook
Dim codeMod As CodeModule
Dim code As String
Dim fso As Scripting.FileSystemObject
Dim folder As folder
Dim name As String, file As String

Application.ScreenUpdating = False

Set fso = New FileSystemObject
Set folder = fso.GetFolder("C:\folder\here")
name = nameOfFileHere
file = folder & "\" & name

Set toF = Workbooks.Open(file)
'modify ThisWorkbook  to place it elsewhere
Set codeMod = toF.VBProject.VBComponents("ThisWorkbook").CodeModule

'erase everything if code already exists
If codeMod.CountOfLines > 0 Then
    codeMod.DeleteLines 1, codeMod.CountOfLines
End If

'dump in new code
code = _
"Private Sub Workbook_Open()" & vbNewLine & _
"   Dim user as String" & vbNewLine & _
"   Dim target as String" & vbNewLine & _
"   user = Application.UserName" & vbNewLine & _
"   target = """ & findUser & """" & vbNewLine & _
"   If user = target then" & vbNewLine & _
"   MsgBox ""I just dumped in some code.""" & vbNewLine & _
"   End if" & vbNewLine & _
"End Sub" & vbNewLine

With codeMod
    .InsertLines .CountOfLines + 1, code
End With

Application.ScreenUpdating = True