AddIn

[Robin Clay]

Greetings !

I'm afraid I have plagued you all for some while with what
seemed to be intractable problems, and I have been very
grateful for frequent very useful advice from many of you -
advice which alas too often seemed not to work - for me.

I THINK I have now discovered the problem: Some while
ago, I asked about AddIn files, and I then took the file I
had and "SavedAs" what I thought was an AddIn, by simply
changing the suffix from .xls to .xla, rather than
clicking on the row beneath, asking which type of file to
save it "As". I have ended up with a quasi-Addin, that
sometimes works as a true AddIn, but sometimes not, and it
may also be this that has caused frequent crashes of Excel.

It was a spreadsheet, with half-a-dozen worksheets, all of
which worked O.K. - so in THIS respect, it acted just like
a worksheet.

I am now trying to separate the "Worksheet" part from
the "AddIn" part.

O.K. - so here is my current challenge !

In the AddIn part, I have modules -
"Constants", "CommonVariables", "VariableTypes"
and "PublicDeclarations", but when I RUN the two parts
together, i.e. when I load the spreadsheet, Excel runs OK
until it reaches "HIP(Ali, 1)", which refers to an array
variable declared in the "Common Variable" module
by "Public HIP(2, 50) As HorizIntersPts"

At this point, Excel creaks to a halt with the message -
"Compile Error - Sub or Function not defined".

Now, this worked FINE until I split the two parts.

What am I doing wrong ?

 

[Tom Ogilvy]

I suspect Excel does not have a definition for

HorizIntersPts

 

[Robin Clay]

I wrote in here, 2003-09-01 08:15:31 PST

I am now trying to separate the "Worksheet"
part from the "AddIn" part.

O.K. - so here is my current challenge !

In the AddIn part, I have modules -
"Constants", "CommonVariables", "VariableTypes"
and "PublicDeclarations", but when I RUN the
two parts together, i.e. when I load the
spreadsheet, Excel runs OK until it reaches
RecLen = Len(HIP(Ali, 1))

and Excel highlights the word "HIP" (it appears to be
in "de-bug" mode, while loading the AddIn)

which refers to an array variable declared
in the "CommonVariables" module by
Public HIP(2, 50) As HorizIntersPts

At this point, Excel creaks to a halt with the message -
"Compile Error - Sub or Function not defined".

Now, this worked FINE until I split the two parts.

What am I doing wrong ?

Tom Ogilvy was (once again) kind enough to respond, 2003-
09-01 08:37:44 PST, for which, many thanks.

I suspect Excel does not have a definition for
HorizIntersPts

I have the lines -

Public Type HorizIntersPts
PtNo As Integer
Rad As Double
Code As String
Cant As Double
End Type

in the "VariableTypes" Module of the AddIn.
It all used to work "before"...
It seems that Excel simply does not reach those lines
early enough ?


When I try to exit, Excel again grinds to a halt at the
same place, and then it stops again in the "BeforeClose"
routine of the AddIn, at this line (when I want the
question "Save this file?" asked -

Ans$ = MsgBox(Msg, vbQuestion + vbYesNoCancel)

Excel highlights the word Ans$ with the message

Compile error
Variable not defined

This, despite my having this line -
Public Ans$
in the "CommonVariables" Module of the AddIn.


Curiously, these are the only two places it seems to have
a problem, yet there are lots of similar situations /
variables....

 

[Myrna Larson]

Do you have Rob Bovey's Code Cleaner? I suggest you run it. Search Google for a URL to download
it. If you can't find it, export all modules to text files, perhaps save, close and reopen the
workbook, then import the modules. (Rob's Add-in does that automatically.)

 

[Robin Clay]

Grateful thanks to both Myrna Larson and Tom Ogilvy

Myrna wrote
and Tom responded

Rob Bovey's URL:
http://www.appspro.com

So thither I went, and fetched it down, and ran it.

Now when I fire up Excel, there is the Code Cleaner
attached, and it immediately generates an error -

Run-time Error '1004':
Method 'VBE' of object '_Application' failed

on this (the last) line of Auto_Open

''' Create the VBE menu.
Set gclsMenuHandler = New CMenuHandler

NOT a very auspicious beginning....

 

[Dave Peterson]

Tools|Macro|Security|Trusted Sources Tab|
There's a checkbox at the bottom of that dialog.

 

[Robin Clay]

Tom Ogilvy responded, 9/2/2003 11:10:39 AM

Rob Bovey's URL:

So I have now managed to run that. Alas ! It makes no
difference to the problem in hand, my code still refuses
to run.

I would also make my addin a workbook,

Please tell me how I do that?

then make it the active project (in
the vbe) and then go to tools=>References
(in the vbe) and make sure nothing
is shown as missing.

At the moment, the code (that all used to run OK, when it
was in a psuedo .xla) now does not recognise the Common
Variables that I have declared in the new "genuine" .xla,
using these lines, and a lot more similar -

Public I0(20) As Double
Public Direction(3) As String
Public G1(32) As String * 8
Public Stxt$(10)
Public Ttxt$(5)


Taking the original "psuedo" .xla, I tried to separate
the "Worksheet" part from the "true" .xla part, but this
has meant that I have duplicated some of the code, in
the "ThisWorkbook" module, specifically, the
Workbook_BeforeClose procedure. I can't see that this
should have the effect at start up that I am
experiencing?