Workbook with vba code only

[Don Bk3]

Personal.xlsb contains code only, and no visible worksheets. Can I do that with another workbook?

Here's why I want to. I developed a set of macros that replicate the old F4 function of repeating a custom search. I may want to share the code, and recommend that the code be placed in personal.xslb.

But, for my own workspace, I keep the code in another folder, where it can be backed up by dropbox, and versioned with Subversion. So, my own personal.xlsb contains only Workbooks.Open "C:\Data\Dropbox\Software....SortCode.xlsm"

This works reasonable well, but the drawback is that the SortCode workbook is required (as far as I can tell) to have one visible worksheet that remains open as long as I want to use the macros.

Is there a better way? I'd like to keep the code less obtrusive.

 

[Dim Me as xlNoob]

Not actually an answer to your question directly, but a different approach to what you want to achieve.

Have you considered leaving it in a separate module in your personal.xslb and with a before close event having the module backed up to the dropbox folder you want synced? You can version it by day or by the hour or whatever and have it overwrite something if it was already backed up that day. Somthething like:

Sub Auto_Close()

 

[Dim Me as xlNoob]

Sorry I was writing code in the editor and hit TAB then ENTER haha

Sub Auto_Close()
    On Error Resume Next
    ThisWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 0
    Call backup
End Sub
Sub backup()
    Dim MyProj As VBProject
    Dim MyMod As VBComponent
    Dim fldrBackup As String
    Dim backupTime As Date
    
    backupTime = Now
    
    fldrBackup = "\\serverpath\UserName\My Documents\html\"
    
    Set MyProj = Application.VBE.VBProjects(2) ' This is what my personal.xlsb was indexed as, yours might be 1 or something different
    
    Set MyMod = MyProj.VBComponents("[Module Name Here]")
    
    MyMod.Export fldrBackup & MyMod.Name & ".bas"


End Sub

Hope this helps. Also I encourage any experts to help out with anything I might have stuffed up in this code....

 

[Dim Me as xlNoob]

I really need to learn how to edit posts...

I missed adding the time in the backup name as to version the module:

MyMod.Export fldrBackup & MyMod.Name & Format(backupTime, "yyyymmdd") & ".bas"

I apologise for posting three in a row with bits and pieces. I'm sorry mods!

 

[Don Bk3]

Thanks! That's exactly what I was looking for. But, I won't bother with the timestamp because I let Subversion do the versioning for me.