Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-16
14
Medium Priority
?
480 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1600 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 400 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

705 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