excel - Creating a self install macro?

Question

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!

Thanks!

Answer 1

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