Posted to microsoft.public.excel.programming
|
|
Could MVP's please review these Instructions
Section A.
Why don't you do the code signing activity, then Section A is redundant.
Although I've never been through the process myself (I do intend to
soonish), I believe this can be done without Microsoft assistance, is not
time consuming and is the cost of a single digital certificate. Can anyone
confirm?
There's a trusting process the user goes through (installing your
certificate) which then allows them to open your workbook without macro
warnings while security is set to Medium or Higher.
http://msdn.microsoft.com/library/en...olut ions.asp
http://www.microsoft.com/technet/col...n/5min-402.asp
Personally, I won't set lower than Medium for *any* reason.
Section B.
There is a section on my website for checking for Broken References.
This can be run first thing as a check. Display a warning if there are
broken references present.
--
Rob van Gelder - http://www.vangelder.co.nz/excel
"DennisE" wrote in message
...
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.
|