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

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

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

8 thoughts on “VBHelpers Update”

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) ?

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. :(

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

Martin: What suggestion was that?

Hi Dick,
I have been using your vbhelpers.xla for many years.
It’s a great tool.
Are you still maintaining it? I noticed that the last update was in 2012, according to dailydoseofexcel.com/?s=vbhelpers

I have now been forced to update my computer to Office 365 64-bit.

I now can’t load the xla, because of invalid api declarations.

But worse than that, macros developed using vbhelpers.xla on my old 32-bit PC, now crash (that is Excel crashes) when run on my 64-Bit system.

The crashes show up in event viewer, as
Faulting Application: Excel.exe
Faulting module : NTDLL.DLL or OLEAUT32.DLL

I have found that the crashes seem to be caused by the special attributes added to a parent class module by vbhelpers.xla.
Attribute NewEnum.VB_UserMemId = – 4
Attribute NewEnum.VB_MemberFlags = “40”

Removing them stops the crashing, but I lose the Default Property functionality (For Each x in Me).

Has anyone else had these problems?
Is there a workaround?

Thanks

•••

Peter: Glad to hear you’re using it. I use it almost every day, but I don’t think I’ve made any changes to it in quite a while.

I tried 64-bit about six years ago and had to go back to 32-bit. I just could never figure out how to do certain things I rely on. I don’t recall experiencing the problem with the NewEnums but I probably didn’t have it installed long enough to see every problem it caused.

I’m going to reach out to the Rubberduck guys and see what they know. They seem to know a lot about programming in the VBE.

https://twitter.com/rubberduckvba/status/1095343100451409920
https://twitter.com/rubberduckvba/status/1095343974267662336
https://twitter.com/rubberduckvba/status/1095345117924347904

If I’m reading this right, they’re saying it shouldn’t cause a crash. Have you tried exporting the modules and reimporting them?

As for the API, all that code is still in there, but I don’t even use it anymore. I just delete all the Parent related stuff. But it wouldn’t be too difficult to update the code to get rid of it.

I took the custom collection crashing 64-Bit Excel issue to MS support. They were able to reproduce the problem on their PC’s.
They also came up with a work around, which has fixed the issue for me.
Insert a DoEvents before every For Each object in custom collection statement.
E. G.
….
DoEvents
For Each clsMyClass In objMyCustomCollection
…..
Next

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

8 thoughts on “VBHelpers Update”

Leave a Reply