Outside code for Excel 2003 macros

Question

Does someone know how to write VBA code in outside file, which is "imported" into Excel 2003 VBA macro every time you run this macro in Excel - like self updating code?

The idea is I write code in outside editor (VIm), and every time I start Excel macro, macro checks for newer data, import that code inside if necessary and then run it (may be one or more functions/subs).

The steps are: open Excel, open XLS file, click on the button, the macro starts, checks for newer outside data (code I have written in outside editor), if data is new, delete old code, import outside code and then actually run macro itself.

Anyone?

http://stackoverflow.com/questions/729895/programatically-add-module-to-xls-2003-using-vbscript — Anders Lindahl, May 29 '12 at 08:44
Advanced text editor I use to write all kind of stuff including Visual Basic code. It is very usefull once you learn how to use it - like external editor. [VIm editor](http://www.vim.org/) — MrB, May 29 '12 at 16:34

score 0 · Answer 1 · edited May 08 '18 at 21:49

Assuming your using this VIM (a text editor) to write .bas files, you can then use the many, many functions for overwriting modules or procedures from this site Programming the VBE.

So, your structure would like something like this:

Sub MyMacro()

Overwrite MyModule in this Workbook with MyModule in .bas file (you could do line count checks or something if you need to, or just overwrite each time to ensure latest code

Call MyModule

End Sub

There are obvious checks you want to incorporate, I would think, to ensure that your latest code always works with the workbook... checks for newer outside data (code I have written in outside editor), if data is new, delete old code, import outside code and then actually run macro itself.

score 0 · Accepted Answer · edited May 08 '18 at 21:49

Found solution (partly from http://www.cpearson.com/excel/vbe.aspx) 10x Scott:

Sub AddOutsideCode()
    Dim VBProjekt As VBIDE.VBProject
    Dim VBKomponenta As VBIDE.VBComponent
    Set VBProjekt = ThisWorkbook.VBProject
    Call DeleteAllButThis(ThisWorkbook, "Fixed")
    Set VBKomponenta = VBProjekt.VBComponents.Import(Filename:="C:\bat\bat.bas")
    VBKomponenta.Name = "OutsideCode"
End Sub

'Delete all modules but named
Private Sub DeleteAllButThis(ByVal wb As Workbook, ByVal CompName As String)
    Application.DisplayAlerts = False
    On Error Resume Next
    For Each komponenta In ThisWorkbook.VBProject.VBComponents
        If komponenta.Name <> CompName Then
            If komponenta.Type = vbext_ct_StdModule Then
                ThisWorkbook.VBProject.VBComponents.Remove komponenta
            End If
        End If
    Next komponenta
    On Error GoTo 0
    Application.DisplayAlerts = True
End Sub

But how to check if the outside code changed or not?

Different timestamp - pull in the code: [Outside code changed](http://stackoverflow.com/questions/10823444/excel-2003-vba-outside-text-file-change/) — MrB, May 31 '12 at 16:42

Outside code for Excel 2003 macros

2 Answers2