VBHelpers Update

If you’re using my VBHelpers add-in and haven’t made these fixes yourself, you may want to download the latest version at the end of this post.

Fix Public to Private Set

When I convert public variables to private and it’s an object, I would call the property Public Property Set, but I didn’t use the Set keyword in the actual variable assignment statement. So I fixed that.

Make FillFromRange use a Variant array

My old FillFromRange used a For Each rCell in rRng.Columns(1).Cells to loop through the cells. Now it read the range into a variant array and uses For i = LBound(vaValues,1) to UBound(vaValues,1). I never had a huge performance problem with it, but generated code should use best practices, don’t you think?

Insert Module Bug

Whenever I would insert a new module, it would put it in the project for the active workbook, not the active project. I finally decided to figure out the problem and fix it. The old code looks like this:

Sub InsertModule()

Dim sName As String
Dim vbc As VBComponent
Dim vbp As VBProject

sName = Application.InputBox("Enter Module Name")

On Error Resume Next

If Left$(sName, 1) = "M" Then
Set vbc = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule)
vbc.Name = sName
vbc.CodeModule.InsertLines vbc.CodeModule.CountOfLines + 1, "Private Const msMODULE As String = """ & sName & "()"""
ElseIf Left$(sName, 1) = "C" Then
Set vbc = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_ClassModule)
vbc.Name = sName
vbc.CodeModule.InsertLines vbc.CodeModule.CountOfLines + 1, "Public " & Mid$(sName, 2, Len(sName)) & "ID As Long"
ElseIf Left$(sName, 1) = "U" Then
Set vbc = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_MSForm)
vbc.Name = sName
End If

End Sub

The InputBox call made the Excel Application active (rather than the VBE) which switches the active project to the active workbook. By storing the active project before I show the InputBox, it does the right thing.

Sub InsertModule()

Dim sName As String
Dim vbc As VBComponent
Dim vbp As VBProject

Set vbp = Application.VBE.ActiveVBProject

sName = Application.InputBox("Enter Module Name")

On Error Resume Next

If Left$(sName, 1) = "M" Then
Set vbc = vbp.VBComponents.Add(vbext_ct_StdModule)
vbc.Name = sName
vbc.CodeModule.InsertLines vbc.CodeModule.CountOfLines + 1, "Private Const msMODULE As String = """ & sName & "()"""
ElseIf Left$(sName, 1) = "C" Then
Set vbc = vbp.VBComponents.Add(vbext_ct_ClassModule)
vbc.Name = sName
vbc.CodeModule.InsertLines vbc.CodeModule.CountOfLines + 1, "Public " & Mid$(sName, 2, Len(sName)) & "ID As Long"
ElseIf Left$(sName, 1) = "U" Then
Set vbc = vbp.VBComponents.Add(vbext_ct_MSForm)
vbc.Name = sName
End If

End Sub

You can download VBHelpers.zip

4 thoughts on “VBHelpers Update

  1. Hello,

    using a french version of excel, the plugin was failing to create an entry in the command bar.
    I had to translate
    With Application.VBE.CommandBars(“Command bar”).Controls(“Tools”)
    to
    With Application.VBE.CommandBars(“Barre de menus”).Controls(“Outils”)

    in AddNewVBEControls.

    This is quite unsatisfactory :)

    This leads to my question : is there a way to access the command bars with something language agnostic (GID or whatever) ?

  2. Oh, oh! Your current version excludes the enhancements I had made to the find capability. So, now I cannot simply replace what is on my computer with your updated version. Bummer. :(

  3. Dick, not related to this post. Just googled VBA web query no results, and somehow landed on a suggestion you made a year ago.

    Is there a possibility that a website where I can enter some parameters and returns a list of items, might not return any records when trying to create a web query in excel 2003?

    Await a bit of your wisdom…

    THanks,

    Martin

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax