Could MVP's please review these Instructions

[DennisE]

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.

 

[Harlan Grove]

Gentlemen:

....

Looks like it's time for you to ask your boss for the refresher course in
avoiding sexual obtuseness. There are a few female MVPs, but I suppose you
wouldn't pay attention to their answers anyway.

 

[Bob Phillips]

Harlan,

That's a big leap from what may only be a case of sloppy addressing to a
full-blown accusation of bigotry.

Not defending. just pointing out the lack of evidence for that statement.

Bob

 

[Seth]

You may want to take the course with DennisE! LOL!

You wrote: "There are a few female MVPs..." LOL! Maybe
there are several! Maybe there are more female than
male! How patronizing to say "there are a few..."

Too funny.


Let *me* be the proper one:

MVPs, please help DennisE if you are so inclined.



Seth

 

[Rob van Gelder]

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/e...italcertificatestoproducetrustedsolutions.asp
http://www.microsoft.com/technet/columns/security/5min/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.

 

[JE McGimpsey]

I wouldn't call it patronizing. Out of the 75 or so XL MVPs, 5 are
female that I know of. It's possible that several of the MVPs that
support asian language communities are female as well, but I'm not good
enough with transliterated asian names to tell. In any case, a distinct
minority.

Other technologies have less lopsided numbers (a couple as much as 50%,
though they're small groups), while some are exclusively male.

You can check out the list here if you're interested:

http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpaward

 

[Dave Peterson]

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.

 

[Ken Wright]

I think the real shame about the OPs note though (Unintential though, I'm sure),
is that there appears to be an assumption that only MVPs can help in this
matter. I might have the tag, but it wasn't for the VBA side of Excel that's
for sure ( Hopefully getting a lot better though :-> ). There are some amazing
minds in here, and not all with an MVP tag (Same goes for the Non-VBA side of
Excel), so to me it's really the ability to construct an adequate response that
hits the mark, that should be the only measure of quality used by the OP or
anyone else for that matter.

Just my £0.02p worth

 

[Bob Phillips]

I think the real shame about the OPs note though (Unintential though, I'm sure),
is that there appears to be an assumption that only MVPs can help in this
matter. I might have the tag, but it wasn't for the VBA side of Excel that's
for sure ( Hopefully getting a lot better though :-> ). There are some amazing
minds in here, and not all with an MVP tag (Same goes for the Non-VBA side of
Excel), so to me it's really the ability to construct an adequate response that
hits the mark, that should be the only measure of quality used by the OP or
anyone else for that matter.

Absolutely agree. Foir instance Harlan himself is a not an MVP (and would
probably never accept such a thing), but we all know his strengths.

What also 'bugs' me is when posters target specific people in the subject.
This is fine for giving thanks for a solution, evenn then it is only
necessary if it is outside the original thread, but slights the many other
contributors but impicily suggesing that it is beyond them.

Just my £0.02p worth

What's with this recent addition? Very PC. This reminds me of my wife who,
every time I say something that she doesn't agree with she fires back 'In
Your Opinion'. Of course it is, there are no such things as facts (IMO<G>),
so everything I say is my opinion, I don't need to say it explicitly every
time. So what I am suggesting is you don't need to say it every time, it
appears apologetic when you have no cause or reason to be.

 

[Bob Phillips]

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.

Good stuff, someone actually addressing the OP's origiunal question - we had
all forgotten.

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:

A different suggestion, declare all Excel/App/VBA constants (such as
vbReadOnly, xlHidden) as constants in your code, and then you can leave that
bit of code alone, still referencing through a constant. Like this

Const olBCC As Long = 3
Sub Macro1()
Dim olApp As Object

Set olApp = CreateObject("Outlook.Application")
Debug.Print olBCC
End Sub

 

[Harald Staff]

What also 'bugs' me is when posters target specific people in the subject.
This is fine for giving thanks for a solution, evenn then it is only
necessary if it is outside the original thread, but slights the many other
contributors but impicily suggesing that it is beyond them.

Hi Bob

I think that anyone should be free to ask for things like "could a readheaded female with
Arapaho ancestors and a wooden leg please review my macros" without bugging the rest of
us. It may not be the best and fastest way to receive the finest of free support, but so
what ?

 

[Ken Wright]

LOL

 

[Bob Phillips]

Hi Harald,

I put bugs in apostrophes, as I cannot say that it really bothers me, I just
feel that it is a bit at odds with the spirit of the groups. After all, when
a true discussion gets going, an OP is likely to get responses from tens of
people.

As for '... female with Arapaho ancestors and a wooden leg ...', is there
something you're trying to tell us here Harald<vbg>

Bob

 

[Ken Wright]

As for '... female with Arapaho ancestors and a wooden leg ...', is there

something you're trying to tell us here Harald<vbg>

Who told him about my other on-line persona, that's what I want to know