Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting VB Code for Excel 2000 to Excel 2003
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").Dele te Set Menu = Application.CommandBars(1).Controls.Add(Type:=msoC ontrolPopup, _ Befo=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting VB Code for Excel 2000 to Excel 2003
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. -- Jim Rech Excel MVP "MarkPirks" wrote in message ... 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").Dele te Set Menu = Application.CommandBars(1).Controls.Add(Type:=msoC ontrolPopup, _ Befo=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting VB Code for Excel 2000 to Excel 2003
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. Mark wrote: 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 "Jim Rech" wrote: 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. -- Jim Rech Excel MVP -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help converting .txt(notepad) file to Excel (2000) | Excel Discussion (Misc queries) | |||
Upgrade from Excel 2000 to Excel 2003 without MS Office 2003? | Excel Discussion (Misc queries) | |||
Code-signing certificate problems in Excel 2000 with Windows 2000 | Excel Programming | |||
Excel 2000 - VBA - Stop recordset Data from auto converting | Excel Programming | |||
Converting Excel 97 macros to 2000 | Excel Programming |