Converting VB Code for Excel 2000 to Excel 2003

[Guest]

sepecifically this macro has stopped working

Function Macro_Menu()
Dim vbcomp As VBComponent
Dim curMacro As String, newMacro As String
Dim i As Integer
Dim Menu As CommandBarPopup
Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim FirstExists As Boolean

On Error Resume Next

Application.CommandBars(1).Controls("Macros").Delete
Set Menu = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, _
Before:=10, Temporary:=True)
Menu.Caption = "Macros"

curMacro = ""

For Each vbcomp In ThisWorkbook.VBProject.VBComponents

If Right(vbcomp.Name, 7) <> "No_Menu" Then
If vbcomp.CodeModule.CountOfLines > 4 Then
If vbcomp.DesignerID <> "Forms.Form" Then

FirstExists = False
For i = 1 To vbcomp.CodeModule.CountOfLines
issuea = Right(vbcomp.CodeModule.Lines(i, 1), 7)
newMacro = vbcomp.CodeModule.ProcOfLine(i, vbext_pk_Proc)

If curMacro <> newMacro Then
curMacro = newMacro
If curMacro <> "" Then
If issuea <> "No Menu" Then
If Not FirstExists Then
Set MenuItem = Menu.Controls.Add(Type:=msoControlPopup)
MenuItem.Caption = vbcomp.Name
FirstExists = True
End If
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = newMacro
SubMenuItem.OnAction = vbcomp.Name & "." & newMacro
End If
End If
End If
Next
End If
End If
End If
Next

Exit_CWBM:
Exit Function
Err_CWBM:
On Error Resume Next
Resume Exit_CWBM

End Function

 

[Jim Rech]

Code that runs in 2000 should run in 2003. And your code did run in 2003
for me once I set a reference to VBA Extensibility and got rid of Option
Explicit because you didn't declare all your variables (not a good
practice).

Btw, it's best if you state what/where the error or problem occurs to simply
the task of helping you.

 

[Guest]

Thanks for your post but could you explain to me "VBA Extensibility" and
"Removing Option explicit". - I'm afraid I'm self taught and just use what
works and Option explicit seems to be needed for some reason in most code.

Mark

 

[Dave Peterson]

Option Explicit
at the top of each of your modules is your friend--it's never actually required
by VBA, but if you don't use it, you can have trouble.

It tells excel that you're going to declare all your variables. Jim wrote that
he removed this from his test module because your code didn't "DIM" all your
variables (and he didn't want to take the time to do it in his test).

If you have
Option Explicit
at the top of your code, you'll spend less time searching for typos like:

myValue = 5
....later
myVa1ue = 12

(The lower case L (ell) is a 1 (one) in bottom version.)

If you have to declare your variables:

Dim myValue as long

then with this kind of typo, your code won't even get close to running.

==========
There are some things in excel that aren't always necessary and don't need to be
loaded for each workbook.

One of those things provides the ability to access your code
programmably/programmatically.

But you can turn it on for any workbook that needs it.

Inside the VBE with your workbook's project the active project:
tools|references
scroll down the list and put a check mark next to that "microsoft Visual basic
for applications extensibility x.x" entry.