Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn
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 ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn
I suspect Excel does not have a definition for
HorizIntersPts -- Regards, Tom Ogilvy Robin Clay wrote in message ... 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 ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn
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.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn
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.) On Tue, 2 Sep 2003 02:07:32 -0700, "Robin Clay" wrote: 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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn
Grateful thanks to both Myrna Larson and Tom Ogilvy
Myrna wrote Do you have Rob Bovey's Code Cleaner? 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.... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn
Tools|Macro|Security|Trusted Sources Tab|
There's a checkbox at the bottom of that dialog. Robin Clay wrote: Rob Bovey's URL: ... and THEN, when I click on Tools Code Cleaner, I get Programmatic access to Visual Basic Project is not trusted (CodeCleanerMain) Now, how do I get rid of this "thing" ???? "Remove" is greyed out.... -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
AddIn
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AddIn Excel2007 | Excel Discussion (Misc queries) | |||
How to close an Addin? | Excel Discussion (Misc queries) | |||
MS VB addin err | Excel Worksheet Functions | |||
Addin Help | Excel Discussion (Misc queries) | |||
Addin Password | Excel Discussion (Misc queries) |