Archive for the ‘Modules’ Category.
I'm a full time Excel developer.
That means I spend quite some time writing code in the Visual Basic Editor (VBE).
I don't maintain a real code library, but I do copy lots of code from previous projects into new ones.
Whereas the VBE is quite a nice application to write code in, it does lack some functionality I needed a couple of weeks ago: the ability to do a search and replace operation on multiple lines of code in one go.
For example, many routines in my projects contain an error handling mechanism of some sort. These may look like this:
TidyUp:
On Error GoTo 0
Exit Sub
locErr:
If ReportError(Err.Description, Err.Number, "DoFindReplace", "Module modMain")=vbRetry then
Resume
Else
Resume Next
End If
Now what if I want them to look like this:
TidyUp:
On Error GoTo 0
Exit Sub
locErr:
Select Case ReportError(Err.Description, Err.Number, "DoFindReplace", "Module modMain")
Case vbRetry
Resume
Case vbIgnore
Resume Next
Case vbAbort
Resume TidyUp
End Select
I wanted a tool that would let me replace the part after the IF statement with the part after the Select Case statement.
Well, here is my first go at it:
Excel VBE Multiline Search And Replace
And here is a screenshot:
I often use Excel's macro recorder to perform repetitive tasks.
It's usually a list of cells with a handful of exceptions. That is, I cant just run the macro from start to finish - I have to give each item a brief glance before the macro runs.
At the end of the macro, it's handy to position the selected cell as the start of the next item in the list.
I'll assign a shortcut key to the macro, such as ctrl+w, then use it on demand.
The process becomes simple: look at the item, is it ok? yes, press ctrl+w, next item, is it ok? yes, press ctrl+w... over and over.
You can reassign your shortcut keys from the Macros window:
From Excel's menu: Tools, Macro, Macros... (or hit Alt+F8)
Highlight a macro, then click Options.
More often than not, the macro recorder will give me a good first draft but I'll have to edit it some more from within the VB editor.
While coding the changes, I wondered where Excel stores the shortcut key.
Could it be that Excel recognises the code comment?
So I deleted the comments to be sure. No, it wasn't the comment.
It turns out that the shortcut key is stored in the Code Module, but it's hidden from sight.
Export the Code Module (right-click the Module, click Export File) then open it in Notepad.
You will notice a line that looks like this:
Attribute Macro1.VB_ProcData.VB_Invoke_Func = "w\n14"
As far as I know, there is no documented limit on the size of a module. It seems the Excel development community has settled on 64KB as the size where strange problems start happening. So, as a rule of thumb, if you have a large module, it may be advantageous to split it up into smaller ones.
To check the size of a module, export it to a text file and check that file's size in Windows.
Hmmm, 2KB. I think I'll be okay with this one.