Solved

How to get rid of "Run-time error '35': Sub or Function not defined"?

Posted on 2011-02-16
14
463 Views
Last Modified: 2013-11-05
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?
0
Comment
Question by:iPromoExpert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
14 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 34909288
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
 
LVL 3

Author Comment

by:iPromoExpert
ID: 34909446
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
 
LVL 26

Expert Comment

by:redmondb
ID: 34912449
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:iPromoExpert
ID: 34917543
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
 
LVL 26

Accepted Solution

by:
redmondb earned 400 total points
ID: 34917679
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
 
LVL 3

Author Comment

by:iPromoExpert
ID: 34917863
"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
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 100 total points
ID: 34918367
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
 
LVL 26

Expert Comment

by:redmondb
ID: 34921415
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
 
LVL 3

Assisted Solution

by:iPromoExpert
iPromoExpert earned 0 total points
ID: 34921686
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
 
LVL 3

Author Comment

by:iPromoExpert
ID: 34921807
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
 
LVL 26

Expert Comment

by:redmondb
ID: 34922041
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
 
LVL 26

Expert Comment

by:redmondb
ID: 34922087
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
 
LVL 3

Author Closing Comment

by:iPromoExpert
ID: 34959207
It wasn't an actual solution but I did get a good advices.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34962473
iPromoExpert,

Thanks! I learned from this as well.

Regards,
Brian.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question