VBA errors in 2007 (works fine in 2003)

  • Thread starter Horatio J. Bilge, Jr.
  • Start date

H

Horatio J. Bilge, Jr.

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
 
Ad

Advertisements

C

Chip Pearson

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

Horatio J. Bilge, Jr.

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
 
C

Chip Pearson

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

Horatio J. Bilge, Jr.

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 VBE>Tools>References. 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 said:
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)


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
 
H

Horatio J. Bilge, Jr.

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 Tools>References
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 said:
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)


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
 
Ad

Advertisements

D

Dave Peterson

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.aspx?scid=kb;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5


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 Tools>References
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 said:
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)


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

:

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.

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
 
H

Horatio J. Bilge, Jr.

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 said:
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.aspx?scid=kb;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5


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 Tools>References
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 said:
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.

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

:

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.

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
 
D

Dave Peterson

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



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 said:
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.aspx?scid=kb;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5


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 Tools>References
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

:


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.

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

:

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.

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
 
H

Horatio J. Bilge, Jr.

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 said:
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).



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 said:
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.aspx?scid=kb;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 Tools>References
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

:


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.

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

:

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.

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
 
D

Dave Peterson

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.

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 said:
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).



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

:

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.aspx?scid=kb;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 Tools>References
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

:


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.

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

:

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.

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
 
Ad

Advertisements

H

Horatio J. Bilge, Jr.

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."
 
D

Dave Peterson

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.
 
Ad

Advertisements

D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top