Solved

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

Posted on 2011-02-16
14
450 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
  • 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now