Creating a macro for self-installation?

Question

Hi, I am creating a lot of macros for my employees. The current method that I have for distribution to another computer is included in the vba editor and imports.

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

Thanks!

Answer 1

You need to enable the Microsoft Scripting Runtime library via links. (VBE → Tools → Links. Check the box.)

Basically, you create a line containing the macro code that you want to set. Obviously, a string can be very long with many lines of code, so you may 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:\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