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
