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.
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 Sub

Open in new window


Any ideas how to hide this error or any other solution how to check module availability?
LVL 3
iPromoExpertAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
redmondbConnect With a Mentor Commented:
iPromoExpert,

Sorry my suggestion didn't work (damn fingers!).

"As you can see from my code - I'm trying to handle error 35 and the code should work."

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.

"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?

Cheers,
Brian.
0
 
rspahitzCommented:
not sure about this error, but a possible solution:

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.
0
 
iPromoExpertAuthor Commented:
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.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
redmondbCommented:
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
0
 
iPromoExpertAuthor Commented:
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.
0
 
iPromoExpertAuthor Commented:
"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
0
 
rspahitzConnect With a Mentor Commented:
One other thought....if it works for Brian, maybe have one of those embedded glitches in your file that's hidden inside somewhere.
A way to fix that is to export the data and import it into a new workbook (not a copy).  Then export the VBA and import it.  Essentially "re-install the OS".
0
 
redmondbCommented:
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.
0
 
iPromoExpertConnect With a Mentor Author Commented:
I was able to track and fixed the error. It was weird thing, not related to ProcStartLine - I was comparing current selection address with some named ranges addresses to do some action; after modifying those lines of code, error 35 gone:

ElseIf Union(Target, Range("startdate")).Address = Range("startdate").Address Or _
        Union(Target, Range("enddate")).Address = Range("enddate").Address Then

Open in new window


replaced with

ElseIf Target.Address = Range("startdate").Address Or _
        Target.Address = Range("enddate").Address Then

Open in new window


Also, I found out that use of ProcStartLine and all other VBA project functions require "trust access to the vba project model" security setting enabled, which is disabled by default.

Any other ideas of how I can check availability of the procedure with specific name in the active sheet module that will not require any changes to the default security settings?
0
 
iPromoExpertAuthor Commented:
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.
0
 
redmondbCommented:
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-compiled version, BUT the macro names are in plaintext. You could search the binary from within your macro (or use something like strings.exe (from the SysInternals section on Microsoft.com)).

Please don't despise me.

Regards,
Brian.
0
 
redmondbCommented:
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.
0
 
iPromoExpertAuthor Commented:
It wasn't an actual solution but I did get a good advices.
0
 
redmondbCommented:
iPromoExpert,

Thanks! I learned from this as well.

Regards,
Brian.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.