Just some thoughts that you can ignore.

Too many words!

I've found that the longer the instructions, the more they're ignored.

Put your instructions in MSWord, add some screen prints and add some white space
to make it easier to read.

And maybe you could modify your code so that instead of using references, you
could use late binding.

Do all your developing with the references so you can get the VBE's nice
intellisense, but before you release it, change your declarations to a more
generic "as object".

And change all the constants to their actual values:

With the references still set, you can hit ctrl-G to see the immediate window
and do this:

?vbext_pp_locked
You'll see that it returns a 1.

By using late binding, you have a lot fewer problems when you share the workbook
with users who use different versions than you developed.

And just as a warning. If at all possible, you'll want to develop with the
oldest version of excel that your users have. Sometimes, it's easy to forget
that a certain feature was added when.


DennisE wrote:

Gentlemen:

I am finding that a number of my clients to whom I distribute
an Excel/VBA software program named TAPS-XL are encountering
difficulty in getting it to load and/or run. To help matters, I have
drawn up instructions to help them resolve such difficulties as
best I can. I am only an intermediate-level Excel programmer,
so would appreciate review, input, ideas, or anything else any
advanced or major league Excel programmers could contribute
to what I've developed below. In case you're wondering, the
TAPS-XL program is distributed free of charge to Federal, State
and Local government agencies, so it's not the case that your
contributions are helping swell the pockets of a profit-oriented,
money-grubbing organization.

-- Dennis Eisen

WHAT TO DO IF TAPS-XL DOESN'T LOAD OR RUN

A. Dealing with Security Issues
The TAPS-XL program, like many Excel workbooks, contains macros. A macro is a
small program written in VBA (Visual Basic for Applications) code that can run
when you start the program or click a control button. Because of the
possibility that a macro can contain malicious code or a virus, Excel will
alert you to whenever it's the case that a workbook happens to contain macros.
The actions you can take depend on the associated security level that's been
set and to some extent on the version of Excel that you're using.

If your Excel security level has been set to High (or Very High in Excel 2003),
Excel workbooks containing macros will not be permitted to run unless they have
been pre-certified by Microsoft (a fairly time-consuming and expensive
process).

If your Excel security level has been set to Medium, you will be given a choice
to run Excel workbooks containing macros with either the macros enabled or
disabled. For workbooks that you've obtained from trusted sources (such as the
TAPS-XL program from Dennis Eisen & Associates), you can safely elect to run
with the macros enabled. Because virtually all of the computations and data
manipulation within TAPS-XL is done through macros rather than classical cell
formulas, TAPS-XL can only run with its macros enabled.

If your Excel security level has been set to Low, Excel will not issue any
warning message to the effect that a workbook contains macros, and will
automatically enable the macros contained in the Excel workbook. In general,
this setting should only be used if a current, up-to-date virus checker is
present and activated.

Excel 2002 or 2003
If when TAPS-XL is loaded, a message appears stating that the macros have
automatically been disabled, you will have to lower the security level from
High or Very High to some lower level. If the Visual Basic toolbar is
displayed, you can start the process by clicking the Security control. If not,
open the Tools menu, click on Options, select the Security tab, click on Macro
Security, select the Security Level tab, and then lower the security level to
either Medium or Low. Do not click OK yet, however, for adjusting the security
level is only half the story. You must also tell Excel that it can trust access
to the project level code whenever this need arises during TAPS-XL execution.
To do this, select the Trusted Sources tab (labeled Trusted Publishers in Excel
2003) and check the box marked Trust access to Visual Basic Project. TAPS-XL
does not contain any add-ins or templates, so the box marked Trust all
installed add-ins and templates can be left checked or unchecked as far as
TAPS-XL is concerned. You will note that at the bottom of the window is
displayed the message Virus Scanner(s) installed if that is indeed the case,
and Excel will automatically use your scanner program to check for viruses
every time you open a workbook. You may now click OK twice in succession to
return to Excel. Then close out Excel and reload TAPS-XL again. If you're
prompted to save any changes to TAPS-XL, you may click "No," as the changes you
just made in the security level are saved within Excel.

It may happen that the security level has already been set to medium or low,
but the box marked Trust access to Visual Basic Project is not yet checked. In
that situation, Excel will issue the message Programmatic access to Visual
Basic Project is not trusted. If this occurs, you will have to click the
Security control, or in the alternative, open the Tools menu, click on Options,
select the Security tab, click on Macro Security, select the Trusted Sources
(or Trusted Publishers) tab and check the box marked Trust access to Visual
Basic Project.

Excel 2000
Unlike Excel 2002/2003, no message appears stating that the macros have been
disabled if the security level has been set to High. The only indication that
this is the case in Excel 2000 is if the initial "Loading€¦" spreadsheet comes
up and stays there for more than a very few seconds as TAPS-XL attempts to load
itself. To lower the security level from High to some lower level, click on the
Security control that's on the Visual Basic toolbar, click on Macro Security,
select the Security Level tab, and then lower the security level to either
Medium or Low. Under Excel 2000, there is no provision for explicitly
indicating that Excel can or cannot trust access to the project level code
whenever the need arises (it simply does). TAPS-XL does not contain any add-ins
or templates, so the box marked Trust all installed add-ins and templates under
the Trusted Sources tab can be left checked or unchecked as far as TAPS-XL is
concerned. You will note that at the bottom of the window is displayed the
message Virus Scanner(s) installed if that is indeed the case, and Excel will
automatically use your scanner program to check for viruses every time you open
a workbook. You may now click OK twice in succession to return to Excel. Then
close out Excel and reload TAPS-XL again. If you're prompted to save any
changes to TAPS-XL, you may click "No," as the changes you just made in the
security level are saved within Excel.

B. Compile-Time Errors
TAPS-XL is an Excel program, and as such requires the presence of Microsoft's
Excel program and accompanying object libraries to run properly. Should any of
these associated object libraries become corrupted, or the links to them become
broken, Excel will stop TAPS-XL and issue a compile-time error. This can occur
during the loading process or during execution.

Should a compile-time error message be encountered, click "OK," and then click
on the Visual Basic Editor (VBE) control that's on the toolbar. If the VBE
control is not on the toolbar, click on any blank space on any toolbar and
select the Visual Basic toolbar from the drop-down list presented. Once the VBE
is selected, click on Tools and then on References. There should be six Object
Libraries that are checked (where xx will be 9.0, 10.0, or 11.0 for Excel 2000,
2002, or 2003), as follows:

Visual Basic for Applications (vbe6.dll);
Microsoft Excel xx Project Library (EXCEL.exe);
Microsoft Forms 2.0 Object Library (FM20.dll);
Microsoft Office xx Object Library (MSO.dll);
Microsoft Visual Basic for Applications Extensibility 5.3 (VBE6EXT.olb); and
OLE Automation (stdole2.tlb).

If any of these are unchecked, find them among the list presented and check
them accordingly. If they are not among the list or the link to them is broken
(as indicated by an "Is Missing" notice), Microsoft Office (not just Excel)
will have to be reinstalled.

It may happen that all six object libraries are present and the links to them
unbroken, but one or more of them have become corrupted over time. If during
execution TAPS-XL hangs up on such built-in VBA functions as CHR( ), LEFT( ),
RIGHT( ), MID( ), SPACE( ), etc., then it's either vbe6.dll or EXCEL.exe that's
been corrupted. If during execution TAPS-XL hangs up on the type assignment
"bln as Balloon," then it's MSO.dll that's missing or has been corrupted. If
during execution TAPS-XL hangs up on the command "LoadPicture," then it's
stole2.tlb that's missing or has been corrupted. And if during execution
TAPS-XL hangs up on "vbext_pp_locked," then it's VBE6EXT.olb that's missing or
has been corrupted.

If any of these files are missing or have been corrupted, try reinstallation of
Microsoft Office (not just Excel). Then with Excel closed, use Windows Explorer
to find and open the Temp directory, then delete those files in the Temp
directory and in any of its subdirectories that appear to be related to Excel
or VBA, and then reboot.

--

Dave Peterson