-
Feb 21st, 2015, 07:03 AM
#1
Thread Starter
Hyperactive Member
Excel VBA - calling a function located in a worksheet code module
In a normal VBA module I have the following (as an example):
Code:
Sub Call_Function_Test
Dim lngNumber as Long
lngNumber = WorksheetFunction(10)
MsgBox (lngNumber)
End Sub
In the code module for a worksheet called Tracker I have the function:
Code:
Public Function WorksheetFunction (ByVal input As Long) as Long
WorksheetFunction = input * 5
End Function
Which should send a value of 10 to the function, multiple by 5, return 50 and then display a message box showing "50". However all that happens is I get the error: Compile error: Sub or function not defined.
If the Public Function is in a normal module it works so I'm guessing that VBA can't "see" the function if it's in a worksheet code module. Is this true? If so how do I make it be "seen" in the code?
Cheers
-Rob
http://www.sudsolutions.com
-
Feb 21st, 2015, 07:53 AM
#2
Re: Excel VBA - calling a function located in a worksheet code module
to call a function in an object module from code, you would address the function by modulename (or any sheet object name) .functionname
i do not believe you can do this with worksheet functions as they need to be available to all sheets by default
anyway, good practise to put functions in a standard module, keep object modules for events and related code
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|