创建自安装宏? [英] Creating a self install macro?

问题描述

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

Hello I create many macros for my co workers. The current method I have for distributing to another computer is going into the vba editor and importing.

我真的很想为宏制作一种安装程序"，让用户无需进入编辑器即可安装新的宏.我不确定这是否可行，但欢迎提出任何想法！

I would really like to make a kind of "installer" for macros that would allow the user to install a new macro without having to go into the editor. I'm not sure this is even possible but any ideas are welcome!

谢谢！

推荐答案

您需要在引用下启用 Microsoft Scripting Runtime 库.(VBE -> 工具 -> 参考资料.选中该框.)

You need to enable Microsoft Scripting Runtime library under references. (VBE -> Tools -> References. Check the box.)

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

Basically, you create a string that holds the code of the macro you want to install. Obviously, the string could be really long with many lines of code so you might need several string variables.

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:folderhere")
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

这篇关于创建自安装宏?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！