StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I've just setup a spreadsheet that uses Bazaar, with manual checkin/out via TortiseBZR. Given that the topic helped me with the save portion, I wanted to post my solution here.</p> <p><em>The solution for me was to create a spreadsheet that exports all modules on save, and removes and re-imports the modules on open. Yes, this could be potentially dangerous for converting existing spreadsheets.</em></p> <p>This allows me to edit the macros in the modules via <strong>Emacs</strong> (yes, emacs) or natively in Excel, and commit my BZR repository after major changes. Because all the modules are text files, the standard diff-style commands in BZR work for my sources except the Excel file itself.</p> <p>I've setup a directory for my BZR repository, X:\Data\MySheet. In the repo are MySheet.xls and one .vba file for each of my modules (ie: Module1Macros). In my spreadsheet I've added one module that is exempt from the export/import cycle called "VersionControl". Each module to be exported and re-imported must end in "Macros".</p> <p><strong>Contents of the "VersionControl" module:</strong></p> <pre class="lang-vb prettyprint-override"><code>Sub SaveCodeModules() 'This code Exports all VBA modules Dim i%, sName$ With ThisWorkbook.VBProject For i% = 1 To .VBComponents.Count If .VBComponents(i%).CodeModule.CountOfLines > 0 Then sName$ = .VBComponents(i%).CodeModule.Name .VBComponents(i%).Export "X:\Tools\MyExcelMacros\" & sName$ & ".vba" End If Next i End With End Sub Sub ImportCodeModules() With ThisWorkbook.VBProject For i% = 1 To .VBComponents.Count ModuleName = .VBComponents(i%).CodeModule.Name If ModuleName <> "VersionControl" Then If Right(ModuleName, 6) = "Macros" Then .VBComponents.Remove .VBComponents(ModuleName) .VBComponents.Import "X:\Data\MySheet\" & ModuleName & ".vba" End If End If Next i End With End Sub </code></pre> <p>Next, we have to setup event hooks for open / save to run these macros. In the code viewer, right click on "ThisWorkbook" and select "View Code". You may have to pull down the select box at the top of the code window to change from "(General)" view to "Workbook" view.</p> <p><strong>Contents of "Workbook" view:</strong></p> <pre class="lang-vb prettyprint-override"><code>Private Sub Workbook_Open() ImportCodeModules End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) SaveCodeModules End Sub </code></pre> <p>I'll be settling into this workflow over the next few weeks, and I'll post if I have any problems.</p> <p>Thanks for sharing the VBComponent code!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload