Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
I have a workbook which uses Chip Pearson's sheet visibility method of
ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
I just copied the code as is on the web page and ran it in 2007. It
works exactly as advertised. When you get an error like "can't find project or library", that means that one of the references in VBA is broken. As often as not, the function that VBA says it can't find (in your case, the "Mid" function) isn't in the library that is broken. But since the compiler is choking on some variable or function, it tells you that some reference is out of whack. In VBA, go to the Tools menu and choose References. See if any references are marked "MISSING". If a reference is missing, you have three choices. If it is a primary library used natively by Excel/VBA (e.g., VBA, Excel, Office, or OLE), you can typically fix it by running Excel with the /regserver switch. Close all applications, then go to the Windows Start menu, choose Run, and enter "C:\Program Files\Microsoft Office\Office12\Excel.exe" /regserver The full path name needs to be in quotes and there is a space between the closing quote and the / character. You may need to change the folder specification to point to where you have Excel installed. The /regserver switch causes Excel to start, and rewrite all of its registry keys and associations back to "factory defaults". This can cure any number of ills. You may loose some customization, but that is a minor issue compared to getting the references back on track. If the library marked MISSING is one that you don't need, uncheck it. If it isn't referenced, VBA isn't going to care about it. If the library marked MISSING is one that you do need, then you need to re-install the program that created the reference. To determine which program screwed things up, in VBA press CTRL G to display the Immediate window, and there, type ?ThisWorkbook.VBProject.References(5).Description and press Enter. Then type ?Thisworkbook.VBProject.References(5).FullPath and press Enter. Change the 5 to the position in the References list at which the offending reference appears. If that doesn't help you find what the problematic program is, type ?ThisWorkbook.VBProject.References(5).GUID and press enter. Copy the GUID from the Immediate into the clipboard. Then, from the Windows Start menu, choose Run, and enter RegEdit.exe In RegEdit, go to the Edit menu, choose Find, and paste in the GUID that you copied from the Excel window. You can often track down somewhat obfuscated or hidden program via their GUID in the Registry. You may have to hit F3 a few times to find all occurrences of the GUID in the registry. Note: Unless you really know what you are doing, do NOT change anything in the Registry via RegEdit. Doing so can cause problems ranging from minor annoyances all the way to not being able to start the computer. Everything is RegEdit is "live". There is no Undo or Close Without Save. Based on the Description and directory path displayed in the Immediate window, or from the GUID entries in the registry, you can probably figure out what program is responsible for the library that is causing the grief. Either un-install/re-install that program or contact the vendor for an update. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 13:03:01 -0700, Horatio J. Bilge, Jr. wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
Chip,
Thanks for the detailed answer. Here is what I found: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). I tried to use the Immediate Window in VBE as you suggested, but I got an error message, "Programmatic access to Visual Basic Project is not trusted." I tried unchecking the missing reference (ATPVBAEN.XLA) and checking atpvbaen.xls instead. That seemed to fix the problem with Excel2007, but when I tried it in Excel2003, I got the same problem that I used to get with Excel2007. ~ Horatio "Chip Pearson" wrote: I just copied the code as is on the web page and ran it in 2007. It works exactly as advertised. When you get an error like "can't find project or library", that means that one of the references in VBA is broken. As often as not, the function that VBA says it can't find (in your case, the "Mid" function) isn't in the library that is broken. But since the compiler is choking on some variable or function, it tells you that some reference is out of whack. In VBA, go to the Tools menu and choose References. See if any references are marked "MISSING". If a reference is missing, you have three choices. If it is a primary library used natively by Excel/VBA (e.g., VBA, Excel, Office, or OLE), you can typically fix it by running Excel with the /regserver switch. Close all applications, then go to the Windows Start menu, choose Run, and enter "C:\Program Files\Microsoft Office\Office12\Excel.exe" /regserver The full path name needs to be in quotes and there is a space between the closing quote and the / character. You may need to change the folder specification to point to where you have Excel installed. The /regserver switch causes Excel to start, and rewrite all of its registry keys and associations back to "factory defaults". This can cure any number of ills. You may loose some customization, but that is a minor issue compared to getting the references back on track. If the library marked MISSING is one that you don't need, uncheck it. If it isn't referenced, VBA isn't going to care about it. If the library marked MISSING is one that you do need, then you need to re-install the program that created the reference. To determine which program screwed things up, in VBA press CTRL G to display the Immediate window, and there, type ?ThisWorkbook.VBProject.References(5).Description and press Enter. Then type ?Thisworkbook.VBProject.References(5).FullPath and press Enter. Change the 5 to the position in the References list at which the offending reference appears. If that doesn't help you find what the problematic program is, type ?ThisWorkbook.VBProject.References(5).GUID and press enter. Copy the GUID from the Immediate into the clipboard. Then, from the Windows Start menu, choose Run, and enter RegEdit.exe In RegEdit, go to the Edit menu, choose Find, and paste in the GUID that you copied from the Excel window. You can often track down somewhat obfuscated or hidden program via their GUID in the Registry. You may have to hit F3 a few times to find all occurrences of the GUID in the registry. Note: Unless you really know what you are doing, do NOT change anything in the Registry via RegEdit. Doing so can cause problems ranging from minor annoyances all the way to not being able to start the computer. Everything is RegEdit is "live". There is no Undo or Close Without Save. Based on the Description and directory path displayed in the Immediate window, or from the GUID entries in the registry, you can probably figure out what program is responsible for the library that is causing the grief. Either un-install/re-install that program or contact the vendor for an update. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 13:03:01 -0700, Horatio J. Bilge, Jr. wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). That is correct, an abbreviation for. Analysis Tool Pack VBA ENglish. The ATPVBAEN.XLA project is a wrapper library for the Analysis Tool Pak allowing you to call functions in the ATP from within VBA. If you are not calling functions in the ATP from VBA, you do not need this add-in and you can uncheck it in the list of Add-Ins that you get from the Excel Tools menu, Add-Ins item. As add-ins, the "Analysis Took Pack" and "Analysis Took Pack - VBA" are entirely independent of one another. You can use one without the other. If you are going to use ATP functions only on worksheets (not in VBA), you don't need to load "Analysis Tool Pack - VBA". If you are going to use ATP functions only in VBA, not on worksheets, you need only load the "Analysis Tool Pack - VBA" add-in and leave the "Analysis Tool Pack" (non-VBA) unloaded. You need both only when you are going to call ATP functions BOTH from worksheets and from VBA. If you do need to call ATP functions from VBA, you need this add-in loaded. In Excel list of Add-Ins, it is the one named "Analysis Took Pak - VBA". Once you load that add-in, you need to go to the Tools menu in VBA, choose References, and select "atpvbaen.xls". Why MS decided to leave the ".xls" in the name is anyone's guess. It is not an XLS workbook. It is an XLA add-in whose name just happens to have the string ".xls" in the name. Once you have checked that reference in VBA References dialog, you can call the functions in that library as if they were native VBA functions. E.g., Debug.Print MRound(12, 5) If there is the possibility of a name collision (two or more libraries having a type or function with the same name), you need to prefix the function name with the library name. Since the ATP VBA library name contains a period, you need to enclose the library name is square brackets. E.g., Debug.Print [atpvbaen.xls].MRound(12, 5) In the line above, note that the library name has XLS, not XLA, even though it really is an XLA, not an XLS. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 19:41:01 -0700, Horatio J. Bilge, Jr. wrote: Chip, Thanks for the detailed answer. Here is what I found: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). I tried to use the Immediate Window in VBE as you suggested, but I got an error message, "Programmatic access to Visual Basic Project is not trusted." I tried unchecking the missing reference (ATPVBAEN.XLA) and checking atpvbaen.xls instead. That seemed to fix the problem with Excel2007, but when I tried it in Excel2003, I got the same problem that I used to get with Excel2007. ~ Horatio "Chip Pearson" wrote: I just copied the code as is on the web page and ran it in 2007. It works exactly as advertised. When you get an error like "can't find project or library", that means that one of the references in VBA is broken. As often as not, the function that VBA says it can't find (in your case, the "Mid" function) isn't in the library that is broken. But since the compiler is choking on some variable or function, it tells you that some reference is out of whack. In VBA, go to the Tools menu and choose References. See if any references are marked "MISSING". If a reference is missing, you have three choices. If it is a primary library used natively by Excel/VBA (e.g., VBA, Excel, Office, or OLE), you can typically fix it by running Excel with the /regserver switch. Close all applications, then go to the Windows Start menu, choose Run, and enter "C:\Program Files\Microsoft Office\Office12\Excel.exe" /regserver The full path name needs to be in quotes and there is a space between the closing quote and the / character. You may need to change the folder specification to point to where you have Excel installed. The /regserver switch causes Excel to start, and rewrite all of its registry keys and associations back to "factory defaults". This can cure any number of ills. You may loose some customization, but that is a minor issue compared to getting the references back on track. If the library marked MISSING is one that you don't need, uncheck it. If it isn't referenced, VBA isn't going to care about it. If the library marked MISSING is one that you do need, then you need to re-install the program that created the reference. To determine which program screwed things up, in VBA press CTRL G to display the Immediate window, and there, type ?ThisWorkbook.VBProject.References(5).Description and press Enter. Then type ?Thisworkbook.VBProject.References(5).FullPath and press Enter. Change the 5 to the position in the References list at which the offending reference appears. If that doesn't help you find what the problematic program is, type ?ThisWorkbook.VBProject.References(5).GUID and press enter. Copy the GUID from the Immediate into the clipboard. Then, from the Windows Start menu, choose Run, and enter RegEdit.exe In RegEdit, go to the Edit menu, choose Find, and paste in the GUID that you copied from the Excel window. You can often track down somewhat obfuscated or hidden program via their GUID in the Registry. You may have to hit F3 a few times to find all occurrences of the GUID in the registry. Note: Unless you really know what you are doing, do NOT change anything in the Registry via RegEdit. Doing so can cause problems ranging from minor annoyances all the way to not being able to start the computer. Everything is RegEdit is "live". There is no Undo or Close Without Save. Based on the Description and directory path displayed in the Immediate window, or from the GUID entries in the registry, you can probably figure out what program is responsible for the library that is causing the grief. Either un-install/re-install that program or contact the vendor for an update. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 13:03:01 -0700, Horatio J. Bilge, Jr. wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
Chip,
Thanks again for the detailed answer. The workbook uses ATP functions on the worksheets, and I'm pretty sure I didn't use any in the VBA - I skimmed through the code and nothing stood out. I went ahead and unchecked the reference to atpvbaen.xls in VBEToolsReferences. I haven't thoroughly tested it yet, but it seems to be working fine now in both 2003 and 2007. ~ Horatio PS - I have turned to your website for information countless times, and have used a number of the macros that you provide on the site. They have helped me create some very useful tools in excel. I am very grateful for the information, and I always reference your site in the documentation of any file that I distribute. "Chip Pearson" wrote: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). That is correct, an abbreviation for. Analysis Tool Pack VBA ENglish. The ATPVBAEN.XLA project is a wrapper library for the Analysis Tool Pak allowing you to call functions in the ATP from within VBA. If you are not calling functions in the ATP from VBA, you do not need this add-in and you can uncheck it in the list of Add-Ins that you get from the Excel Tools menu, Add-Ins item. As add-ins, the "Analysis Took Pack" and "Analysis Took Pack - VBA" are entirely independent of one another. You can use one without the other. If you are going to use ATP functions only on worksheets (not in VBA), you don't need to load "Analysis Tool Pack - VBA". If you are going to use ATP functions only in VBA, not on worksheets, you need only load the "Analysis Tool Pack - VBA" add-in and leave the "Analysis Tool Pack" (non-VBA) unloaded. You need both only when you are going to call ATP functions BOTH from worksheets and from VBA. If you do need to call ATP functions from VBA, you need this add-in loaded. In Excel list of Add-Ins, it is the one named "Analysis Took Pak - VBA". Once you load that add-in, you need to go to the Tools menu in VBA, choose References, and select "atpvbaen.xls". Why MS decided to leave the ".xls" in the name is anyone's guess. It is not an XLS workbook. It is an XLA add-in whose name just happens to have the string ".xls" in the name. Once you have checked that reference in VBA References dialog, you can call the functions in that library as if they were native VBA functions. E.g., Debug.Print MRound(12, 5) If there is the possibility of a name collision (two or more libraries having a type or function with the same name), you need to prefix the function name with the library name. Since the ATP VBA library name contains a period, you need to enclose the library name is square brackets. E.g., Debug.Print [atpvbaen.xls].MRound(12, 5) In the line above, note that the library name has XLS, not XLA, even though it really is an XLA, not an XLS. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 19:41:01 -0700, Horatio J. Bilge, Jr. wrote: Chip, Thanks for the detailed answer. Here is what I found: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). I tried to use the Immediate Window in VBE as you suggested, but I got an error message, "Programmatic access to Visual Basic Project is not trusted." I tried unchecking the missing reference (ATPVBAEN.XLA) and checking atpvbaen.xls instead. That seemed to fix the problem with Excel2007, but when I tried it in Excel2003, I got the same problem that I used to get with Excel2007. ~ Horatio "Chip Pearson" wrote: I just copied the code as is on the web page and ran it in 2007. It works exactly as advertised. When you get an error like "can't find project or library", that means that one of the references in VBA is broken. As often as not, the function that VBA says it can't find (in your case, the "Mid" function) isn't in the library that is broken. But since the compiler is choking on some variable or function, it tells you that some reference is out of whack. In VBA, go to the Tools menu and choose References. See if any references are marked "MISSING". If a reference is missing, you have three choices. If it is a primary library used natively by Excel/VBA (e.g., VBA, Excel, Office, or OLE), you can typically fix it by running Excel with the /regserver switch. Close all applications, then go to the Windows Start menu, choose Run, and enter "C:\Program Files\Microsoft Office\Office12\Excel.exe" /regserver The full path name needs to be in quotes and there is a space between the closing quote and the / character. You may need to change the folder specification to point to where you have Excel installed. The /regserver switch causes Excel to start, and rewrite all of its registry keys and associations back to "factory defaults". This can cure any number of ills. You may loose some customization, but that is a minor issue compared to getting the references back on track. If the library marked MISSING is one that you don't need, uncheck it. If it isn't referenced, VBA isn't going to care about it. If the library marked MISSING is one that you do need, then you need to re-install the program that created the reference. To determine which program screwed things up, in VBA press CTRL G to display the Immediate window, and there, type ?ThisWorkbook.VBProject.References(5).Description and press Enter. Then type ?Thisworkbook.VBProject.References(5).FullPath and press Enter. Change the 5 to the position in the References list at which the offending reference appears. If that doesn't help you find what the problematic program is, type ?ThisWorkbook.VBProject.References(5).GUID and press enter. Copy the GUID from the Immediate into the clipboard. Then, from the Windows Start menu, choose Run, and enter RegEdit.exe In RegEdit, go to the Edit menu, choose Find, and paste in the GUID that you copied from the Excel window. You can often track down somewhat obfuscated or hidden program via their GUID in the Registry. You may have to hit F3 a few times to find all occurrences of the GUID in the registry. Note: Unless you really know what you are doing, do NOT change anything in the Registry via RegEdit. Doing so can cause problems ranging from minor annoyances all the way to not being able to start the computer. Everything is RegEdit is "live". There is no Undo or Close Without Save. Based on the Description and directory path displayed in the Immediate window, or from the GUID entries in the registry, you can probably figure out what program is responsible for the library that is causing the grief. Either un-install/re-install that program or contact the vendor for an update. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 13:03:01 -0700, Horatio J. Bilge, Jr. wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
The file is working as intended now in Excel 2003 and 2007. However, I have
encountered problems using it on a Mac. I am using Office 2004 for Mac, and I get a compile error: "Sub or Function not defined." The function that is highlighted is "Split" (VisibleArr = Split(S, ":")). The ToolsReferences dialog does not indicate any missing references. I don't like having to use a Mac at work but I'm stuck with it. Thanks for any help. ~ Horatio "Chip Pearson" wrote: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). That is correct, an abbreviation for. Analysis Tool Pack VBA ENglish. The ATPVBAEN.XLA project is a wrapper library for the Analysis Tool Pak allowing you to call functions in the ATP from within VBA. If you are not calling functions in the ATP from VBA, you do not need this add-in and you can uncheck it in the list of Add-Ins that you get from the Excel Tools menu, Add-Ins item. As add-ins, the "Analysis Took Pack" and "Analysis Took Pack - VBA" are entirely independent of one another. You can use one without the other. If you are going to use ATP functions only on worksheets (not in VBA), you don't need to load "Analysis Tool Pack - VBA". If you are going to use ATP functions only in VBA, not on worksheets, you need only load the "Analysis Tool Pack - VBA" add-in and leave the "Analysis Tool Pack" (non-VBA) unloaded. You need both only when you are going to call ATP functions BOTH from worksheets and from VBA. If you do need to call ATP functions from VBA, you need this add-in loaded. In Excel list of Add-Ins, it is the one named "Analysis Took Pak - VBA". Once you load that add-in, you need to go to the Tools menu in VBA, choose References, and select "atpvbaen.xls". Why MS decided to leave the ".xls" in the name is anyone's guess. It is not an XLS workbook. It is an XLA add-in whose name just happens to have the string ".xls" in the name. Once you have checked that reference in VBA References dialog, you can call the functions in that library as if they were native VBA functions. E.g., Debug.Print MRound(12, 5) If there is the possibility of a name collision (two or more libraries having a type or function with the same name), you need to prefix the function name with the library name. Since the ATP VBA library name contains a period, you need to enclose the library name is square brackets. E.g., Debug.Print [atpvbaen.xls].MRound(12, 5) In the line above, note that the library name has XLS, not XLA, even though it really is an XLA, not an XLS. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 19:41:01 -0700, Horatio J. Bilge, Jr. wrote: Chip, Thanks for the detailed answer. Here is what I found: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). I tried to use the Immediate Window in VBE as you suggested, but I got an error message, "Programmatic access to Visual Basic Project is not trusted." I tried unchecking the missing reference (ATPVBAEN.XLA) and checking atpvbaen.xls instead. That seemed to fix the problem with Excel2007, but when I tried it in Excel2003, I got the same problem that I used to get with Excel2007. ~ Horatio "Chip Pearson" wrote: I just copied the code as is on the web page and ran it in 2007. It works exactly as advertised. When you get an error like "can't find project or library", that means that one of the references in VBA is broken. As often as not, the function that VBA says it can't find (in your case, the "Mid" function) isn't in the library that is broken. But since the compiler is choking on some variable or function, it tells you that some reference is out of whack. In VBA, go to the Tools menu and choose References. See if any references are marked "MISSING". If a reference is missing, you have three choices. If it is a primary library used natively by Excel/VBA (e.g., VBA, Excel, Office, or OLE), you can typically fix it by running Excel with the /regserver switch. Close all applications, then go to the Windows Start menu, choose Run, and enter "C:\Program Files\Microsoft Office\Office12\Excel.exe" /regserver The full path name needs to be in quotes and there is a space between the closing quote and the / character. You may need to change the folder specification to point to where you have Excel installed. The /regserver switch causes Excel to start, and rewrite all of its registry keys and associations back to "factory defaults". This can cure any number of ills. You may loose some customization, but that is a minor issue compared to getting the references back on track. If the library marked MISSING is one that you don't need, uncheck it. If it isn't referenced, VBA isn't going to care about it. If the library marked MISSING is one that you do need, then you need to re-install the program that created the reference. To determine which program screwed things up, in VBA press CTRL G to display the Immediate window, and there, type ?ThisWorkbook.VBProject.References(5).Description and press Enter. Then type ?Thisworkbook.VBProject.References(5).FullPath and press Enter. Change the 5 to the position in the References list at which the offending reference appears. If that doesn't help you find what the problematic program is, type ?ThisWorkbook.VBProject.References(5).GUID and press enter. Copy the GUID from the Immediate into the clipboard. Then, from the Windows Start menu, choose Run, and enter RegEdit.exe In RegEdit, go to the Edit menu, choose Find, and paste in the GUID that you copied from the Excel window. You can often track down somewhat obfuscated or hidden program via their GUID in the Registry. You may have to hit F3 a few times to find all occurrences of the GUID in the registry. Note: Unless you really know what you are doing, do NOT change anything in the Registry via RegEdit. Doing so can cause problems ranging from minor annoyances all the way to not being able to start the computer. Everything is RegEdit is "live". There is no Undo or Close Without Save. Based on the Description and directory path displayed in the Immediate window, or from the GUID entries in the registry, you can probably figure out what program is responsible for the library that is causing the grief. Either un-install/re-install that program or contact the vendor for an update. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 13:03:01 -0700, Horatio J. Bilge, Jr. wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
Split was added in xl2k (VB(A) 6).
Your Mac doesn't support this version. But there are alternatives: Try adding these functions to a different module and change the split line to: Split97(S, ":") Option Explicit Public Function ReadUntil(ByRef sIn As String, _ sDelim As String, Optional bCompare As Long _ = vbBinaryCompare) As String Dim nPos As String nPos = InStr(1, sIn, sDelim, bCompare) If nPos 0 Then ReadUntil = Left(sIn, nPos - 1) sIn = Mid(sIn, nPos + Len(sDelim)) End If End Function Public Function Split97(ByVal sIn As String, Optional sDelim As _ String, Optional nLimit As Long = -1, Optional bCompare As _ Long = vbBinaryCompare) As Variant Dim sRead As String, sOut() As String, nC As Integer If sDelim = "" Then Split97 = sIn End If sRead = ReadUntil(sIn, sDelim, bCompare) Do ReDim Preserve sOut(nC) sOut(nC) = sRead nC = nC + 1 If nLimit < -1 And nC = nLimit Then Exit Do sRead = ReadUntil(sIn, sDelim) Loop While sRead < "" ReDim Preserve sOut(nC) sOut(nC) = sIn Split97 = sOut End Function The readuntil and split97 functions were stolen from the MSKB: http://support.microsoft.com/default...b;en-us;188007 HOWTO: Simulate Visual Basic 6.0 String Functions in VB5 Horatio J. Bilge, Jr. wrote: The file is working as intended now in Excel 2003 and 2007. However, I have encountered problems using it on a Mac. I am using Office 2004 for Mac, and I get a compile error: "Sub or Function not defined." The function that is highlighted is "Split" (VisibleArr = Split(S, ":")). The ToolsReferences dialog does not indicate any missing references. I don't like having to use a Mac at work but I'm stuck with it. Thanks for any help. ~ Horatio "Chip Pearson" wrote: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). That is correct, an abbreviation for. Analysis Tool Pack VBA ENglish. The ATPVBAEN.XLA project is a wrapper library for the Analysis Tool Pak allowing you to call functions in the ATP from within VBA. If you are not calling functions in the ATP from VBA, you do not need this add-in and you can uncheck it in the list of Add-Ins that you get from the Excel Tools menu, Add-Ins item. As add-ins, the "Analysis Took Pack" and "Analysis Took Pack - VBA" are entirely independent of one another. You can use one without the other. If you are going to use ATP functions only on worksheets (not in VBA), you don't need to load "Analysis Tool Pack - VBA". If you are going to use ATP functions only in VBA, not on worksheets, you need only load the "Analysis Tool Pack - VBA" add-in and leave the "Analysis Tool Pack" (non-VBA) unloaded. You need both only when you are going to call ATP functions BOTH from worksheets and from VBA. If you do need to call ATP functions from VBA, you need this add-in loaded. In Excel list of Add-Ins, it is the one named "Analysis Took Pak - VBA". Once you load that add-in, you need to go to the Tools menu in VBA, choose References, and select "atpvbaen.xls". Why MS decided to leave the ".xls" in the name is anyone's guess. It is not an XLS workbook. It is an XLA add-in whose name just happens to have the string ".xls" in the name. Once you have checked that reference in VBA References dialog, you can call the functions in that library as if they were native VBA functions. E.g., Debug.Print MRound(12, 5) If there is the possibility of a name collision (two or more libraries having a type or function with the same name), you need to prefix the function name with the library name. Since the ATP VBA library name contains a period, you need to enclose the library name is square brackets. E.g., Debug.Print [atpvbaen.xls].MRound(12, 5) In the line above, note that the library name has XLS, not XLA, even though it really is an XLA, not an XLS. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 19:41:01 -0700, Horatio J. Bilge, Jr. wrote: Chip, Thanks for the detailed answer. Here is what I found: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). I tried to use the Immediate Window in VBE as you suggested, but I got an error message, "Programmatic access to Visual Basic Project is not trusted." I tried unchecking the missing reference (ATPVBAEN.XLA) and checking atpvbaen.xls instead. That seemed to fix the problem with Excel2007, but when I tried it in Excel2003, I got the same problem that I used to get with Excel2007. ~ Horatio "Chip Pearson" wrote: I just copied the code as is on the web page and ran it in 2007. It works exactly as advertised. When you get an error like "can't find project or library", that means that one of the references in VBA is broken. As often as not, the function that VBA says it can't find (in your case, the "Mid" function) isn't in the library that is broken. But since the compiler is choking on some variable or function, it tells you that some reference is out of whack. In VBA, go to the Tools menu and choose References. See if any references are marked "MISSING". If a reference is missing, you have three choices. If it is a primary library used natively by Excel/VBA (e.g., VBA, Excel, Office, or OLE), you can typically fix it by running Excel with the /regserver switch. Close all applications, then go to the Windows Start menu, choose Run, and enter "C:\Program Files\Microsoft Office\Office12\Excel.exe" /regserver The full path name needs to be in quotes and there is a space between the closing quote and the / character. You may need to change the folder specification to point to where you have Excel installed. The /regserver switch causes Excel to start, and rewrite all of its registry keys and associations back to "factory defaults". This can cure any number of ills. You may loose some customization, but that is a minor issue compared to getting the references back on track. If the library marked MISSING is one that you don't need, uncheck it. If it isn't referenced, VBA isn't going to care about it. If the library marked MISSING is one that you do need, then you need to re-install the program that created the reference. To determine which program screwed things up, in VBA press CTRL G to display the Immediate window, and there, type ?ThisWorkbook.VBProject.References(5).Description and press Enter. Then type ?Thisworkbook.VBProject.References(5).FullPath and press Enter. Change the 5 to the position in the References list at which the offending reference appears. If that doesn't help you find what the problematic program is, type ?ThisWorkbook.VBProject.References(5).GUID and press enter. Copy the GUID from the Immediate into the clipboard. Then, from the Windows Start menu, choose Run, and enter RegEdit.exe In RegEdit, go to the Edit menu, choose Find, and paste in the GUID that you copied from the Excel window. You can often track down somewhat obfuscated or hidden program via their GUID in the Registry. You may have to hit F3 a few times to find all occurrences of the GUID in the registry. Note: Unless you really know what you are doing, do NOT change anything in the Registry via RegEdit. Doing so can cause problems ranging from minor annoyances all the way to not being able to start the computer. Everything is RegEdit is "live". There is no Undo or Close Without Save. Based on the Description and directory path displayed in the Immediate window, or from the GUID entries in the registry, you can probably figure out what program is responsible for the library that is causing the grief. Either un-install/re-install that program or contact the vendor for an update. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 13:03:01 -0700, Horatio J. Bilge, Jr. wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
Thank you. It appears to be getting through the UnHideSheets macro, but it is
hanging on the next step. After the UnHideSheets macro runs, a userform should pop up. Instead of the form, I get "Run-time error '32809': Application-defined or object-defined error." When I click Debug, the UserForm_Initialize sub is highlighted. The UserForm_Initialize sub just takes a name from the workbook to complete one of the captions on the form: lblMyName.Caption = "Fill in information for " Worksheets("Sheet1").Range("MyName").Value ~ Horatio "Dave Peterson" wrote: Split was added in xl2k (VB(A) 6). Your Mac doesn't support this version. But there are alternatives: Try adding these functions to a different module and change the split line to: Split97(S, ":") Option Explicit Public Function ReadUntil(ByRef sIn As String, _ sDelim As String, Optional bCompare As Long _ = vbBinaryCompare) As String Dim nPos As String nPos = InStr(1, sIn, sDelim, bCompare) If nPos 0 Then ReadUntil = Left(sIn, nPos - 1) sIn = Mid(sIn, nPos + Len(sDelim)) End If End Function Public Function Split97(ByVal sIn As String, Optional sDelim As _ String, Optional nLimit As Long = -1, Optional bCompare As _ Long = vbBinaryCompare) As Variant Dim sRead As String, sOut() As String, nC As Integer If sDelim = "" Then Split97 = sIn End If sRead = ReadUntil(sIn, sDelim, bCompare) Do ReDim Preserve sOut(nC) sOut(nC) = sRead nC = nC + 1 If nLimit < -1 And nC = nLimit Then Exit Do sRead = ReadUntil(sIn, sDelim) Loop While sRead < "" ReDim Preserve sOut(nC) sOut(nC) = sIn Split97 = sOut End Function The readuntil and split97 functions were stolen from the MSKB: http://support.microsoft.com/default...b;en-us;188007 HOWTO: Simulate Visual Basic 6.0 String Functions in VB5 Horatio J. Bilge, Jr. wrote: The file is working as intended now in Excel 2003 and 2007. However, I have encountered problems using it on a Mac. I am using Office 2004 for Mac, and I get a compile error: "Sub or Function not defined." The function that is highlighted is "Split" (VisibleArr = Split(S, ":")). The ToolsReferences dialog does not indicate any missing references. I don't like having to use a Mac at work but I'm stuck with it. Thanks for any help. ~ Horatio "Chip Pearson" wrote: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). That is correct, an abbreviation for. Analysis Tool Pack VBA ENglish. The ATPVBAEN.XLA project is a wrapper library for the Analysis Tool Pak allowing you to call functions in the ATP from within VBA. If you are not calling functions in the ATP from VBA, you do not need this add-in and you can uncheck it in the list of Add-Ins that you get from the Excel Tools menu, Add-Ins item. As add-ins, the "Analysis Took Pack" and "Analysis Took Pack - VBA" are entirely independent of one another. You can use one without the other. If you are going to use ATP functions only on worksheets (not in VBA), you don't need to load "Analysis Tool Pack - VBA". If you are going to use ATP functions only in VBA, not on worksheets, you need only load the "Analysis Tool Pack - VBA" add-in and leave the "Analysis Tool Pack" (non-VBA) unloaded. You need both only when you are going to call ATP functions BOTH from worksheets and from VBA. If you do need to call ATP functions from VBA, you need this add-in loaded. In Excel list of Add-Ins, it is the one named "Analysis Took Pak - VBA". Once you load that add-in, you need to go to the Tools menu in VBA, choose References, and select "atpvbaen.xls". Why MS decided to leave the ".xls" in the name is anyone's guess. It is not an XLS workbook. It is an XLA add-in whose name just happens to have the string ".xls" in the name. Once you have checked that reference in VBA References dialog, you can call the functions in that library as if they were native VBA functions. E.g., Debug.Print MRound(12, 5) If there is the possibility of a name collision (two or more libraries having a type or function with the same name), you need to prefix the function name with the library name. Since the ATP VBA library name contains a period, you need to enclose the library name is square brackets. E.g., Debug.Print [atpvbaen.xls].MRound(12, 5) In the line above, note that the library name has XLS, not XLA, even though it really is an XLA, not an XLS. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 19:41:01 -0700, Horatio J. Bilge, Jr. wrote: Chip, Thanks for the detailed answer. Here is what I found: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). I tried to use the Immediate Window in VBE as you suggested, but I got an error message, "Programmatic access to Visual Basic Project is not trusted." I tried unchecking the missing reference (ATPVBAEN.XLA) and checking atpvbaen.xls instead. That seemed to fix the problem with Excel2007, but when I tried it in Excel2003, I got the same problem that I used to get with Excel2007. ~ Horatio "Chip Pearson" wrote: I just copied the code as is on the web page and ran it in 2007. It works exactly as advertised. When you get an error like "can't find project or library", that means that one of the references in VBA is broken. As often as not, the function that VBA says it can't find (in your case, the "Mid" function) isn't in the library that is broken. But since the compiler is choking on some variable or function, it tells you that some reference is out of whack. In VBA, go to the Tools menu and choose References. See if any references are marked "MISSING". If a reference is missing, you have three choices. If it is a primary library used natively by Excel/VBA (e.g., VBA, Excel, Office, or OLE), you can typically fix it by running Excel with the /regserver switch. Close all applications, then go to the Windows Start menu, choose Run, and enter "C:\Program Files\Microsoft Office\Office12\Excel.exe" /regserver The full path name needs to be in quotes and there is a space between the closing quote and the / character. You may need to change the folder specification to point to where you have Excel installed. The /regserver switch causes Excel to start, and rewrite all of its registry keys and associations back to "factory defaults". This can cure any number of ills. You may loose some customization, but that is a minor issue compared to getting the references back on track. If the library marked MISSING is one that you don't need, uncheck it. If it isn't referenced, VBA isn't going to care about it. If the library marked MISSING is one that you do need, then you need to re-install the program that created the reference. To determine which program screwed things up, in VBA press CTRL G to display the Immediate window, and there, type ?ThisWorkbook.VBProject.References(5).Description and press Enter. Then type ?Thisworkbook.VBProject.References(5).FullPath and press Enter. Change the 5 to the position in the References list at which the offending reference appears. If that doesn't help you find what the problematic program is, type ?ThisWorkbook.VBProject.References(5).GUID and press enter. Copy the GUID from the Immediate into the clipboard. Then, from the Windows Start menu, choose Run, and enter RegEdit.exe In RegEdit, go to the Edit menu, choose Find, and paste in the GUID that you copied from the Excel window. You can often track down somewhat obfuscated or hidden program via their GUID in the Registry. You may have to hit F3 a few times to find all occurrences of the GUID in the registry. Note: Unless you really know what you are doing, do NOT change anything in the Registry via RegEdit. Doing so can cause problems ranging from minor annoyances all the way to not being able to start the computer. Everything is RegEdit is "live". There is no Undo or Close Without Save. Based on the Description and directory path displayed in the Immediate window, or from the GUID entries in the registry, you can probably figure out what program is responsible for the library that is causing the grief. Either un-install/re-install that program or contact the vendor for an update. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 13:03:01 -0700, Horatio J. Bilge, Jr. wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
Is it breaking on the Mac version of excel?
If yes, then I don't believe they support the new (xl97+) userforms. You could still use dialog sheets (maybe???). If you're back in WinTel land, what line causes the trouble--I don't think you posted the complete code for the userform_initialize procedure. If that procedure only consists of that single line, then make a small change to test it: lblMyName.Caption = "Fill in information for " (don't include the rest of the statement) If it blows up there, I'm not sure. If it works with that simplified version of the code, then check to make sure the activeworkbook is what you expect. Make sure that there's a worksheet named Sheet1 in the activeworkbook. And make sure that there's a single cell range with that name (myName) (and that it doesn't contain an error). Horatio J. Bilge, Jr. wrote: Thank you. It appears to be getting through the UnHideSheets macro, but it is hanging on the next step. After the UnHideSheets macro runs, a userform should pop up. Instead of the form, I get "Run-time error '32809': Application-defined or object-defined error." When I click Debug, the UserForm_Initialize sub is highlighted. The UserForm_Initialize sub just takes a name from the workbook to complete one of the captions on the form: lblMyName.Caption = "Fill in information for " Worksheets("Sheet1").Range("MyName").Value ~ Horatio "Dave Peterson" wrote: Split was added in xl2k (VB(A) 6). Your Mac doesn't support this version. But there are alternatives: Try adding these functions to a different module and change the split line to: Split97(S, ":") Option Explicit Public Function ReadUntil(ByRef sIn As String, _ sDelim As String, Optional bCompare As Long _ = vbBinaryCompare) As String Dim nPos As String nPos = InStr(1, sIn, sDelim, bCompare) If nPos 0 Then ReadUntil = Left(sIn, nPos - 1) sIn = Mid(sIn, nPos + Len(sDelim)) End If End Function Public Function Split97(ByVal sIn As String, Optional sDelim As _ String, Optional nLimit As Long = -1, Optional bCompare As _ Long = vbBinaryCompare) As Variant Dim sRead As String, sOut() As String, nC As Integer If sDelim = "" Then Split97 = sIn End If sRead = ReadUntil(sIn, sDelim, bCompare) Do ReDim Preserve sOut(nC) sOut(nC) = sRead nC = nC + 1 If nLimit < -1 And nC = nLimit Then Exit Do sRead = ReadUntil(sIn, sDelim) Loop While sRead < "" ReDim Preserve sOut(nC) sOut(nC) = sIn Split97 = sOut End Function The readuntil and split97 functions were stolen from the MSKB: http://support.microsoft.com/default...b;en-us;188007 HOWTO: Simulate Visual Basic 6.0 String Functions in VB5 Horatio J. Bilge, Jr. wrote: The file is working as intended now in Excel 2003 and 2007. However, I have encountered problems using it on a Mac. I am using Office 2004 for Mac, and I get a compile error: "Sub or Function not defined." The function that is highlighted is "Split" (VisibleArr = Split(S, ":")). The ToolsReferences dialog does not indicate any missing references. I don't like having to use a Mac at work but I'm stuck with it. Thanks for any help. ~ Horatio "Chip Pearson" wrote: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). That is correct, an abbreviation for. Analysis Tool Pack VBA ENglish. The ATPVBAEN.XLA project is a wrapper library for the Analysis Tool Pak allowing you to call functions in the ATP from within VBA. If you are not calling functions in the ATP from VBA, you do not need this add-in and you can uncheck it in the list of Add-Ins that you get from the Excel Tools menu, Add-Ins item. As add-ins, the "Analysis Took Pack" and "Analysis Took Pack - VBA" are entirely independent of one another. You can use one without the other. If you are going to use ATP functions only on worksheets (not in VBA), you don't need to load "Analysis Tool Pack - VBA". If you are going to use ATP functions only in VBA, not on worksheets, you need only load the "Analysis Tool Pack - VBA" add-in and leave the "Analysis Tool Pack" (non-VBA) unloaded. You need both only when you are going to call ATP functions BOTH from worksheets and from VBA. If you do need to call ATP functions from VBA, you need this add-in loaded. In Excel list of Add-Ins, it is the one named "Analysis Took Pak - VBA". Once you load that add-in, you need to go to the Tools menu in VBA, choose References, and select "atpvbaen.xls". Why MS decided to leave the ".xls" in the name is anyone's guess. It is not an XLS workbook. It is an XLA add-in whose name just happens to have the string ".xls" in the name. Once you have checked that reference in VBA References dialog, you can call the functions in that library as if they were native VBA functions. E.g., Debug.Print MRound(12, 5) If there is the possibility of a name collision (two or more libraries having a type or function with the same name), you need to prefix the function name with the library name. Since the ATP VBA library name contains a period, you need to enclose the library name is square brackets. E.g., Debug.Print [atpvbaen.xls].MRound(12, 5) In the line above, note that the library name has XLS, not XLA, even though it really is an XLA, not an XLS. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 19:41:01 -0700, Horatio J. Bilge, Jr. wrote: Chip, Thanks for the detailed answer. Here is what I found: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). I tried to use the Immediate Window in VBE as you suggested, but I got an error message, "Programmatic access to Visual Basic Project is not trusted." I tried unchecking the missing reference (ATPVBAEN.XLA) and checking atpvbaen.xls instead. That seemed to fix the problem with Excel2007, but when I tried it in Excel2003, I got the same problem that I used to get with Excel2007. ~ Horatio "Chip Pearson" wrote: I just copied the code as is on the web page and ran it in 2007. It works exactly as advertised. When you get an error like "can't find project or library", that means that one of the references in VBA is broken. As often as not, the function that VBA says it can't find (in your case, the "Mid" function) isn't in the library that is broken. But since the compiler is choking on some variable or function, it tells you that some reference is out of whack. In VBA, go to the Tools menu and choose References. See if any references are marked "MISSING". If a reference is missing, you have three choices. If it is a primary library used natively by Excel/VBA (e.g., VBA, Excel, Office, or OLE), you can typically fix it by running Excel with the /regserver switch. Close all applications, then go to the Windows Start menu, choose Run, and enter "C:\Program Files\Microsoft Office\Office12\Excel.exe" /regserver The full path name needs to be in quotes and there is a space between the closing quote and the / character. You may need to change the folder specification to point to where you have Excel installed. The /regserver switch causes Excel to start, and rewrite all of its registry keys and associations back to "factory defaults". This can cure any number of ills. You may loose some customization, but that is a minor issue compared to getting the references back on track. If the library marked MISSING is one that you don't need, uncheck it. If it isn't referenced, VBA isn't going to care about it. If the library marked MISSING is one that you do need, then you need to re-install the program that created the reference. To determine which program screwed things up, in VBA press CTRL G to display the Immediate window, and there, type ?ThisWorkbook.VBProject.References(5).Description and press Enter. Then type ?Thisworkbook.VBProject.References(5).FullPath and press Enter. Change the 5 to the position in the References list at which the offending reference appears. If that doesn't help you find what the problematic program is, type ?ThisWorkbook.VBProject.References(5).GUID and press enter. Copy the GUID from the Immediate into the clipboard. Then, from the Windows Start menu, choose Run, and enter RegEdit.exe In RegEdit, go to the Edit menu, choose Find, and paste in the GUID that you copied from the Excel window. You can often track down somewhat obfuscated or hidden program via their GUID in the Registry. You may have to hit F3 a few times to find all occurrences of the GUID in the registry. Note: Unless you really know what you are doing, do NOT change anything in the Registry via RegEdit. Doing so can cause problems ranging from minor annoyances all the way to not being able to start the computer. Everything is RegEdit is "live". There is no Undo or Close Without Save. Based on the Description and directory path displayed in the Immediate window, or from the GUID entries in the registry, you can probably figure out what program is responsible for the library that is causing the grief. Either un-install/re-install that program or contact the vendor for an update. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 13:03:01 -0700, Horatio J. Bilge, Jr. wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
Yes. It's just breaking on the Mac version of Excel. It works as expected on
Excel 2003 and 2007. I did edit the UserForm_Initialize procedure to make it shorter to read, but it is just that one line. The form also has a button to change the name, but it isn't involved in the Initialize procedure. The form basically says, "This form is for [insert name from worksheet]. If this is not you, click 'Change Name' below." I tried changing the UserForm_Initialize procedure as you suggested, and the form then showed up, but when I tried to change the name, I got an error when it tried to unprotect the worksheet to write the new name. I tried unprotecting the sheet manually, to verify that the password was correct, and it unprotected fine, but when I reprotected it, it does not give the same options as 2003 or 2007. There are only three options - protect worksheet for contents, objects, or scenarios. I am getting the feeling that trying to get this to work for the Mac may not be worth the hassle. It may require some major rewriting, just for those poor few who don't have access to a PC. ~ Horatio "Dave Peterson" wrote: Is it breaking on the Mac version of excel? If yes, then I don't believe they support the new (xl97+) userforms. You could still use dialog sheets (maybe???). If you're back in WinTel land, what line causes the trouble--I don't think you posted the complete code for the userform_initialize procedure. If that procedure only consists of that single line, then make a small change to test it: lblMyName.Caption = "Fill in information for " (don't include the rest of the statement) If it blows up there, I'm not sure. If it works with that simplified version of the code, then check to make sure the activeworkbook is what you expect. Make sure that there's a worksheet named Sheet1 in the activeworkbook. And make sure that there's a single cell range with that name (myName) (and that it doesn't contain an error). Horatio J. Bilge, Jr. wrote: Thank you. It appears to be getting through the UnHideSheets macro, but it is hanging on the next step. After the UnHideSheets macro runs, a userform should pop up. Instead of the form, I get "Run-time error '32809': Application-defined or object-defined error." When I click Debug, the UserForm_Initialize sub is highlighted. The UserForm_Initialize sub just takes a name from the workbook to complete one of the captions on the form: lblMyName.Caption = "Fill in information for " Worksheets("Sheet1").Range("MyName").Value ~ Horatio "Dave Peterson" wrote: Split was added in xl2k (VB(A) 6). Your Mac doesn't support this version. But there are alternatives: Try adding these functions to a different module and change the split line to: Split97(S, ":") Option Explicit Public Function ReadUntil(ByRef sIn As String, _ sDelim As String, Optional bCompare As Long _ = vbBinaryCompare) As String Dim nPos As String nPos = InStr(1, sIn, sDelim, bCompare) If nPos 0 Then ReadUntil = Left(sIn, nPos - 1) sIn = Mid(sIn, nPos + Len(sDelim)) End If End Function Public Function Split97(ByVal sIn As String, Optional sDelim As _ String, Optional nLimit As Long = -1, Optional bCompare As _ Long = vbBinaryCompare) As Variant Dim sRead As String, sOut() As String, nC As Integer If sDelim = "" Then Split97 = sIn End If sRead = ReadUntil(sIn, sDelim, bCompare) Do ReDim Preserve sOut(nC) sOut(nC) = sRead nC = nC + 1 If nLimit < -1 And nC = nLimit Then Exit Do sRead = ReadUntil(sIn, sDelim) Loop While sRead < "" ReDim Preserve sOut(nC) sOut(nC) = sIn Split97 = sOut End Function The readuntil and split97 functions were stolen from the MSKB: http://support.microsoft.com/default...b;en-us;188007 HOWTO: Simulate Visual Basic 6.0 String Functions in VB5 Horatio J. Bilge, Jr. wrote: The file is working as intended now in Excel 2003 and 2007. However, I have encountered problems using it on a Mac. I am using Office 2004 for Mac, and I get a compile error: "Sub or Function not defined." The function that is highlighted is "Split" (VisibleArr = Split(S, ":")). The ToolsReferences dialog does not indicate any missing references. I don't like having to use a Mac at work but I'm stuck with it. Thanks for any help. ~ Horatio "Chip Pearson" wrote: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). That is correct, an abbreviation for. Analysis Tool Pack VBA ENglish. The ATPVBAEN.XLA project is a wrapper library for the Analysis Tool Pak allowing you to call functions in the ATP from within VBA. If you are not calling functions in the ATP from VBA, you do not need this add-in and you can uncheck it in the list of Add-Ins that you get from the Excel Tools menu, Add-Ins item. As add-ins, the "Analysis Took Pack" and "Analysis Took Pack - VBA" are entirely independent of one another. You can use one without the other. If you are going to use ATP functions only on worksheets (not in VBA), you don't need to load "Analysis Tool Pack - VBA". If you are going to use ATP functions only in VBA, not on worksheets, you need only load the "Analysis Tool Pack - VBA" add-in and leave the "Analysis Tool Pack" (non-VBA) unloaded. You need both only when you are going to call ATP functions BOTH from worksheets and from VBA. If you do need to call ATP functions from VBA, you need this add-in loaded. In Excel list of Add-Ins, it is the one named "Analysis Took Pak - VBA". Once you load that add-in, you need to go to the Tools menu in VBA, choose References, and select "atpvbaen.xls". Why MS decided to leave the ".xls" in the name is anyone's guess. It is not an XLS workbook. It is an XLA add-in whose name just happens to have the string ".xls" in the name. Once you have checked that reference in VBA References dialog, you can call the functions in that library as if they were native VBA functions. E.g., Debug.Print MRound(12, 5) If there is the possibility of a name collision (two or more libraries having a type or function with the same name), you need to prefix the function name with the library name. Since the ATP VBA library name contains a period, you need to enclose the library name is square brackets. E.g., Debug.Print [atpvbaen.xls].MRound(12, 5) In the line above, note that the library name has XLS, not XLA, even though it really is an XLA, not an XLS. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 19:41:01 -0700, Horatio J. Bilge, Jr. wrote: Chip, Thanks for the detailed answer. Here is what I found: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). I tried to use the Immediate Window in VBE as you suggested, but I got an error message, "Programmatic access to Visual Basic Project is not trusted." I tried unchecking the missing reference (ATPVBAEN.XLA) and checking atpvbaen.xls instead. That seemed to fix the problem with Excel2007, but when I tried it in Excel2003, I got the same problem that I used to get with Excel2007. ~ Horatio "Chip Pearson" wrote: I just copied the code as is on the web page and ran it in 2007. It works exactly as advertised. When you get an error like "can't find project or library", that means that one of the references in VBA is broken. As often as not, the function that VBA says it can't find (in your case, the "Mid" function) isn't in the library that is broken. But since the compiler is choking on some variable or function, it tells you that some reference is out of whack. In VBA, go to the Tools menu and choose References. See if any references are marked "MISSING". If a reference is missing, you have three choices. If it is a primary library used natively by Excel/VBA (e.g., VBA, Excel, Office, or OLE), you can typically fix it by running Excel with the /regserver switch. Close all applications, then go to the Windows Start menu, choose Run, and enter "C:\Program Files\Microsoft Office\Office12\Excel.exe" /regserver The full path name needs to be in quotes and there is a space between the closing quote and the / character. You may need to change the folder specification to point to where you have Excel installed. The /regserver switch causes Excel to start, and rewrite all of its registry keys and associations back to "factory defaults". This can cure any number of ills. You may loose some customization, but that is a minor issue compared to getting the references back on track. If the library marked MISSING is one that you don't need, uncheck it. If it isn't referenced, VBA isn't going to care about it. If the library marked MISSING is one that you do need, then you need to re-install the program that created the reference. To determine which program screwed things up, in VBA press CTRL G to display the Immediate window, and there, type ?ThisWorkbook.VBProject.References(5).Description and press Enter. Then type ?Thisworkbook.VBProject.References(5).FullPath and press Enter. Change the 5 to the position in the References list at which the offending reference appears. If that doesn't help you find what the problematic program is, type ?ThisWorkbook.VBProject.References(5).GUID and press enter. Copy the GUID from the Immediate into the clipboard. Then, from the Windows Start menu, choose Run, and enter RegEdit.exe In RegEdit, go to the Edit menu, choose Find, and paste in the GUID that you copied from the Excel window. You can often track down somewhat obfuscated or hidden program via their GUID in the Registry. You may have to hit F3 a few times to find all occurrences of the GUID in the registry. Note: Unless you really know what you are doing, do NOT change anything in the Registry via RegEdit. Doing so can cause problems ranging from minor annoyances all the way to not being able to start the computer. Everything is RegEdit is "live". There is no Undo or Close Without Save. Based on the Description and directory path displayed in the Immediate window, or from the GUID entries in the registry, you can probably figure out what program is responsible for the library that is causing the grief. Either un-install/re-install that program or contact the vendor for an update. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 13:03:01 -0700, Horatio J. Bilge, Jr. wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
There aren't many things that go wrong with this part of your code:
Worksheets("Sheet1").Range("MyName").Value There may not be a sheet named Sheet1 in the active workbook. There may not be a range named myRange on that sheet. That range may not be exactly one cell. You could verify each: Dim TestWks as worksheet dim TestRng as range on error resume next set testwks = worksheets("Sheet1") set testrng = testwks.range("MyName") on error goto 0 if testwks is nothing then msgbox "Sheet doesn't exist" else if testrng is nothing then msgbox "No range named MyName on: " & testwks.name else if testrng.cells.count < 1 then msgbox "not correct amount of cells in MyRange" else if iserror(testrng.value) then msgbox "A single cell range named myRange has an error in it" end if end if end if end if I'd create a separate procedure that does that work--just to verify that it worked ok or showed an error message. Horatio J. Bilge, Jr. wrote: Yes. It's just breaking on the Mac version of Excel. It works as expected on Excel 2003 and 2007. I did edit the UserForm_Initialize procedure to make it shorter to read, but it is just that one line. The form also has a button to change the name, but it isn't involved in the Initialize procedure. The form basically says, "This form is for [insert name from worksheet]. If this is not you, click 'Change Name' below." I tried changing the UserForm_Initialize procedure as you suggested, and the form then showed up, but when I tried to change the name, I got an error when it tried to unprotect the worksheet to write the new name. I tried unprotecting the sheet manually, to verify that the password was correct, and it unprotected fine, but when I reprotected it, it does not give the same options as 2003 or 2007. There are only three options - protect worksheet for contents, objects, or scenarios. I am getting the feeling that trying to get this to work for the Mac may not be worth the hassle. It may require some major rewriting, just for those poor few who don't have access to a PC. ~ Horatio "Dave Peterson" wrote: Is it breaking on the Mac version of excel? If yes, then I don't believe they support the new (xl97+) userforms. You could still use dialog sheets (maybe???). If you're back in WinTel land, what line causes the trouble--I don't think you posted the complete code for the userform_initialize procedure. If that procedure only consists of that single line, then make a small change to test it: lblMyName.Caption = "Fill in information for " (don't include the rest of the statement) If it blows up there, I'm not sure. If it works with that simplified version of the code, then check to make sure the activeworkbook is what you expect. Make sure that there's a worksheet named Sheet1 in the activeworkbook. And make sure that there's a single cell range with that name (myName) (and that it doesn't contain an error). Horatio J. Bilge, Jr. wrote: Thank you. It appears to be getting through the UnHideSheets macro, but it is hanging on the next step. After the UnHideSheets macro runs, a userform should pop up. Instead of the form, I get "Run-time error '32809': Application-defined or object-defined error." When I click Debug, the UserForm_Initialize sub is highlighted. The UserForm_Initialize sub just takes a name from the workbook to complete one of the captions on the form: lblMyName.Caption = "Fill in information for " Worksheets("Sheet1").Range("MyName").Value ~ Horatio "Dave Peterson" wrote: Split was added in xl2k (VB(A) 6). Your Mac doesn't support this version. But there are alternatives: Try adding these functions to a different module and change the split line to: Split97(S, ":") Option Explicit Public Function ReadUntil(ByRef sIn As String, _ sDelim As String, Optional bCompare As Long _ = vbBinaryCompare) As String Dim nPos As String nPos = InStr(1, sIn, sDelim, bCompare) If nPos 0 Then ReadUntil = Left(sIn, nPos - 1) sIn = Mid(sIn, nPos + Len(sDelim)) End If End Function Public Function Split97(ByVal sIn As String, Optional sDelim As _ String, Optional nLimit As Long = -1, Optional bCompare As _ Long = vbBinaryCompare) As Variant Dim sRead As String, sOut() As String, nC As Integer If sDelim = "" Then Split97 = sIn End If sRead = ReadUntil(sIn, sDelim, bCompare) Do ReDim Preserve sOut(nC) sOut(nC) = sRead nC = nC + 1 If nLimit < -1 And nC = nLimit Then Exit Do sRead = ReadUntil(sIn, sDelim) Loop While sRead < "" ReDim Preserve sOut(nC) sOut(nC) = sIn Split97 = sOut End Function The readuntil and split97 functions were stolen from the MSKB: http://support.microsoft.com/default...b;en-us;188007 HOWTO: Simulate Visual Basic 6.0 String Functions in VB5 Horatio J. Bilge, Jr. wrote: The file is working as intended now in Excel 2003 and 2007. However, I have encountered problems using it on a Mac. I am using Office 2004 for Mac, and I get a compile error: "Sub or Function not defined." The function that is highlighted is "Split" (VisibleArr = Split(S, ":")). The ToolsReferences dialog does not indicate any missing references. I don't like having to use a Mac at work but I'm stuck with it. Thanks for any help. ~ Horatio "Chip Pearson" wrote: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). That is correct, an abbreviation for. Analysis Tool Pack VBA ENglish. The ATPVBAEN.XLA project is a wrapper library for the Analysis Tool Pak allowing you to call functions in the ATP from within VBA. If you are not calling functions in the ATP from VBA, you do not need this add-in and you can uncheck it in the list of Add-Ins that you get from the Excel Tools menu, Add-Ins item. As add-ins, the "Analysis Took Pack" and "Analysis Took Pack - VBA" are entirely independent of one another. You can use one without the other. If you are going to use ATP functions only on worksheets (not in VBA), you don't need to load "Analysis Tool Pack - VBA". If you are going to use ATP functions only in VBA, not on worksheets, you need only load the "Analysis Tool Pack - VBA" add-in and leave the "Analysis Tool Pack" (non-VBA) unloaded. You need both only when you are going to call ATP functions BOTH from worksheets and from VBA. If you do need to call ATP functions from VBA, you need this add-in loaded. In Excel list of Add-Ins, it is the one named "Analysis Took Pak - VBA". Once you load that add-in, you need to go to the Tools menu in VBA, choose References, and select "atpvbaen.xls". Why MS decided to leave the ".xls" in the name is anyone's guess. It is not an XLS workbook. It is an XLA add-in whose name just happens to have the string ".xls" in the name. Once you have checked that reference in VBA References dialog, you can call the functions in that library as if they were native VBA functions. E.g., Debug.Print MRound(12, 5) If there is the possibility of a name collision (two or more libraries having a type or function with the same name), you need to prefix the function name with the library name. Since the ATP VBA library name contains a period, you need to enclose the library name is square brackets. E.g., Debug.Print [atpvbaen.xls].MRound(12, 5) In the line above, note that the library name has XLS, not XLA, even though it really is an XLA, not an XLS. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 19:41:01 -0700, Horatio J. Bilge, Jr. wrote: Chip, Thanks for the detailed answer. Here is what I found: The missing reference is ATPVBAEN.XLA (analysis toolpak, I believe). I tried to use the Immediate Window in VBE as you suggested, but I got an error message, "Programmatic access to Visual Basic Project is not trusted." I tried unchecking the missing reference (ATPVBAEN.XLA) and checking atpvbaen.xls instead. That seemed to fix the problem with Excel2007, but when I tried it in Excel2003, I got the same problem that I used to get with Excel2007. ~ Horatio "Chip Pearson" wrote: I just copied the code as is on the web page and ran it in 2007. It works exactly as advertised. When you get an error like "can't find project or library", that means that one of the references in VBA is broken. As often as not, the function that VBA says it can't find (in your case, the "Mid" function) isn't in the library that is broken. But since the compiler is choking on some variable or function, it tells you that some reference is out of whack. In VBA, go to the Tools menu and choose References. See if any references are marked "MISSING". If a reference is missing, you have three choices. If it is a primary library used natively by Excel/VBA (e.g., VBA, Excel, Office, or OLE), you can typically fix it by running Excel with the /regserver switch. Close all applications, then go to the Windows Start menu, choose Run, and enter "C:\Program Files\Microsoft Office\Office12\Excel.exe" /regserver The full path name needs to be in quotes and there is a space between the closing quote and the / character. You may need to change the folder specification to point to where you have Excel installed. The /regserver switch causes Excel to start, and rewrite all of its registry keys and associations back to "factory defaults". This can cure any number of ills. You may loose some customization, but that is a minor issue compared to getting the references back on track. If the library marked MISSING is one that you don't need, uncheck it. If it isn't referenced, VBA isn't going to care about it. If the library marked MISSING is one that you do need, then you need to re-install the program that created the reference. To determine which program screwed things up, in VBA press CTRL G to display the Immediate window, and there, type ?ThisWorkbook.VBProject.References(5).Description and press Enter. Then type ?Thisworkbook.VBProject.References(5).FullPath and press Enter. Change the 5 to the position in the References list at which the offending reference appears. If that doesn't help you find what the problematic program is, type ?ThisWorkbook.VBProject.References(5).GUID and press enter. Copy the GUID from the Immediate into the clipboard. Then, from the Windows Start menu, choose Run, and enter RegEdit.exe In RegEdit, go to the Edit menu, choose Find, and paste in the GUID that you copied from the Excel window. You can often track down somewhat obfuscated or hidden program via their GUID in the Registry. You may have to hit F3 a few times to find all occurrences of the GUID in the registry. Note: Unless you really know what you are doing, do NOT change anything in the Registry via RegEdit. Doing so can cause problems ranging from minor annoyances all the way to not being able to start the computer. Everything is RegEdit is "live". There is no Undo or Close Without Save. Based on the Description and directory path displayed in the Immediate window, or from the GUID entries in the registry, you can probably figure out what program is responsible for the library that is causing the grief. Either un-install/re-install that program or contact the vendor for an update. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Aug 2009 13:03:01 -0700, Horatio J. Bilge, Jr. wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
I am at home, so I tried your test code in excel2007, and it runs without
errors. I won't be able to try it on the Mac version until next week, but I don't see how it would be different. In looking at my UserForm_Initialize procedure, I wondered if having vbLf in there could cause the error on a Mac: lblMyName.Caption = "This form is for " & Worksheets("Sheet1").Range("MyName").Value & vbLf & "If this is not you, click Change Name below." "Horatio J. Bilge, Jr." wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
Dave et al -
Minor point: Mac Excel 2004 does support the userforms used in Windows Excel 97 and later. - Mike http://www.MikeMiddleton.com "Dave Peterson" wrote in message ... Is it breaking on the Mac version of excel? If yes, then I don't believe they support the new (xl97+) userforms. You could still use dialog sheets (maybe???). If you're back in WinTel land, what line causes the trouble--I don't think you posted the complete code for the userform_initialize procedure. If that procedure only consists of that single line, then make a small change to test it: lblMyName.Caption = "Fill in information for " (don't include the rest of the statement) If it blows up there, I'm not sure. If it works with that simplified version of the code, then check to make sure the activeworkbook is what you expect. Make sure that there's a worksheet named Sheet1 in the activeworkbook. And make sure that there's a single cell range with that name (myName) (and that it doesn't contain an error). -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
I'd try:
msgbox Worksheets("Sheet1").Range("MyName").Value lblMyName.Caption = "This form is for " If either of those doesn't work, then it's not the vblf. But if I were supporting both wintel and Mac's, I'd use vbnewline instead of vblf. Horatio J. Bilge, Jr. wrote: I am at home, so I tried your test code in excel2007, and it runs without errors. I won't be able to try it on the Mac version until next week, but I don't see how it would be different. In looking at my UserForm_Initialize procedure, I wondered if having vbLf in there could cause the error on a Mac: lblMyName.Caption = "This form is for " & Worksheets("Sheet1").Range("MyName").Value & vbLf & "If this is not you, click Change Name below." "Horatio J. Bilge, Jr." wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA errors in 2007 (works fine in 2003)
Just to make sure you understand...
From one of my previous posts in the thread: .....I don't believe they support the new (xl97+) userforms. You could still use dialog sheets (maybe???). And from Mike Middleton's post: Minor point: Mac Excel 2004 does support the userforms used in Windows Excel 97 and later. Dave Peterson wrote: I'd try: msgbox Worksheets("Sheet1").Range("MyName").Value lblMyName.Caption = "This form is for " If either of those doesn't work, then it's not the vblf. But if I were supporting both wintel and Mac's, I'd use vbnewline instead of vblf. Horatio J. Bilge, Jr. wrote: I am at home, so I tried your test code in excel2007, and it runs without errors. I won't be able to try it on the Mac version until next week, but I don't see how it would be different. In looking at my UserForm_Initialize procedure, I wondered if having vbLf in there could cause the error on a Mac: lblMyName.Caption = "This form is for " & Worksheets("Sheet1").Range("MyName").Value & vbLf & "If this is not you, click Change Name below." "Horatio J. Bilge, Jr." wrote: I have a workbook which uses Chip Pearson's sheet visibility method of ensuring that users enable macros (complete code at http://www.cpearson.com/excel/EnableMacros.aspx). The file works well in Excel2003 on my windows xp machine, but when I enable macros in Excel2007 (running on Vista Home Premium), I get an error when the macros run. A hidden module "modRequireMacros" contains two macros - one runs when opening the file (UnHideSheets) and the other runs before closing (SaveStateAndHide). When the UnHideSheets macro runs, the error is "Compile error: Can't find project or library" and "Mid" is highlighted in the following piece of code: S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo S = Mid(S, 4, Len(S) - 4) When the SaveStateAndHide macro runs, I get the same error and "Chr" is highlighted in the following line of code: ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart allignment issue in 2007 but in 2003 its working fine | Charts and Charting in Excel | |||
Charts that printed fine in 2003 don't print right in 2007? | Excel Discussion (Misc queries) | |||
xls file works fine in Excel 2000 and 2007 but crashes on opening in 2003 | Excel Discussion (Misc queries) | |||
Visual Basic Function works fine in Excell 2003 but not in Excel 2 | Excel Discussion (Misc queries) | |||
REF errors when opening excel in xp. works fine when opening wor. | Excel Discussion (Misc queries) |