How to get rid of "Run-time error '35': Sub or Function not defined"?
In my application i have to check if the module with specific name exist in the current worksheet. I'm using CodeModule.ProcStartLine property for this. The problem is that I can't avoid pop-up error window: "Run-time error '35': Sub or Function not defined".
There is no error if module exist.
Any ideas how to hide this error or any other solution how to check module availability?
There is no error if module exist.
Public Sub ShowFloatingButton(ByVal Sh As Object, ByVal Target As Range)
Dim ProcStartLine As Long
'...
On Error GoTo NoSub
ProcStartLine = IIf(IsError(ThisWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule.ProcStartLine("FloatingButton_Click", 0)), 0, ThisWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule.ProcStartLine("FloatingButton_Click", 0))
On Error GoTo 0
'...
NoSub:
If Err= 35 Then
//I'm not getting this message :(
MsgBox Err.Description
End If
End SubAny ideas how to hide this error or any other solution how to check module availability?
ASKER
Hi rspahitz,
I'm using ProcStartLine variable for this purpose. And it should have unique value for each Worksheet based on "FloatingButton_Click" sub availability in that Worksheet's module.
All I need is to check module availability or to do not have pop-up error window for the method I have developed.
I'm using ProcStartLine variable for this purpose. And it should have unique value for each Worksheet based on "FloatingButton_Click" sub availability in that Worksheet's module.
All I need is to check module availability or to do not have pop-up error window for the method I have developed.
iPromoExpert,
This was driving me crazy as it behaved correctly on PC.
Got it! In the VBE screen, select "Tools" on the Menu Bar, then "Options". Select the "General Tab". Change "Error Trapping" to "Break on unhandled errors".
[Your current setting is one of the other two, correct? (Fingers crossed.)]
Regards,
Brian
This was driving me crazy as it behaved correctly on PC.
Got it! In the VBE screen, select "Tools" on the Menu Bar, then "Options". Select the "General Tab". Change "Error Trapping" to "Break on unhandled errors".
[Your current setting is one of the other two, correct? (Fingers crossed.)]
Regards,
Brian
ASKER
Thanks redmondb,
My settings were set to "Break on unhandled errors", even more - any of Error Trapping options doesn't stop this error window. As you can see from my code - I'm trying to handle error 35 and the code should work.
Besides the requirement that application should run on any Excel version (most at least :)) without any settings changes.
My settings were set to "Break on unhandled errors", even more - any of Error Trapping options doesn't stop this error window. As you can see from my code - I'm trying to handle error 35 and the code should work.
Besides the requirement that application should run on any Excel version (most at least :)) without any settings changes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"Yes, that was the whole point of my post. On my PC your code works perfectly. I had to change my settings to get it to break."
Let me test on other machines here.
"Besides the requirement that application should run on any Excel version (most at least :)) without any settings changes."
"That's important to know - maybe it should have been mentioned in the question?"
Isn't this expectation /should run on any Excel version/ every developer has as default? :)
redmondb, thank you again - I'll be back shortly with more updates
Let me test on other machines here.
"Besides the requirement that application should run on any Excel version (most at least :)) without any settings changes."
"That's important to know - maybe it should have been mentioned in the question?"
Isn't this expectation /should run on any Excel version/ every developer has as default? :)
redmondb, thank you again - I'll be back shortly with more updates
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks, iPromoExpert, that'd be good.
"Isn't this expectation /should run on any Excel version/ every developer has as default?"
Fortunately not. This would prevent developers from using Excel improvements. Extreme case - the first version of Excel I used didn't even have VBA (it used <shudder> macrosheets, IIRC). One of the most common questions on EE is "what version"? You weren't asked because you had flagged Excel 2010.
Regards,
Brian.
"Isn't this expectation /should run on any Excel version/ every developer has as default?"
Fortunately not. This would prevent developers from using Excel improvements. Extreme case - the first version of Excel I used didn't even have VBA (it used <shudder> macrosheets, IIRC). One of the most common questions on EE is "what version"? You weren't asked because you had flagged Excel 2010.
Regards,
Brian.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brian, you are absolutely right!
It is always has to be a point that you'll have to choose between use of new functionality and versions support to stick at the moment.
It is always has to be a point that you'll have to choose between use of new functionality and versions support to stick at the moment.
iPromoExpert,
Well spotted, particularly about the trusted access (which I had done on my laptop 2 minutes after installing Office and promptly forgotten about).
I'd be worried about the chances of finding a reasonable work-around for the "security setting", simply because of the word "security".
I know an un-reasonable work-around. It's kind of silly, so if you haven't got a really good sense of humour then please stop reading now.
<Gulp> Unzip the vbaprojects.bin from the xlsm file. This files contains any macros. It's in a binary format (very likely BIFF), the code is in an almost impenetrable pre-semi-kinda-half-compil
Please don't despise me.
Regards,
Brian.
Well spotted, particularly about the trusted access (which I had done on my laptop 2 minutes after installing Office and promptly forgotten about).
I'd be worried about the chances of finding a reasonable work-around for the "security setting", simply because of the word "security".
I know an un-reasonable work-around. It's kind of silly, so if you haven't got a really good sense of humour then please stop reading now.
<Gulp> Unzip the vbaprojects.bin from the xlsm file. This files contains any macros. It's in a binary format (very likely BIFF), the code is in an almost impenetrable pre-semi-kinda-half-compil
Please don't despise me.
Regards,
Brian.
iPromoExpert,
OK, please ignore previous post. You need to know not just that the macro exists (which it would do) but that it's a worksheet macro. That information is in vbaprojects.bin (if not there, where?), but not in a readily identifiable form.
Regards,
Brian.
OK, please ignore previous post. You need to know not just that the macro exists (which it would do) but that it's a worksheet macro. That information is in vbaprojects.bin (if not there, where?), but not in a readily identifiable form.
Regards,
Brian.
ASKER
It wasn't an actual solution but I did get a good advices.
iPromoExpert,
Thanks! I learned from this as well.
Regards,
Brian.
Thanks! I learned from this as well.
Regards,
Brian.
in the module, add a global (public) constant with a value of of something like 1
e.g.
Public Const TestVariable as integer=1
In your code, check to see if this "variable" is 1; if not, then the module doesn't exist.
Note that this will probably not compile if you have Option Explicit included at the top of the module doing the testing.