Excel User Defined Functions hiding

Hi,

How do I go about hiding functions in Excel VBA that I don't want to be sheet UDFs?

e.g.

I want this function to be available in VBA but not in a worksheet UDF manner

Public Function AddTwo (value As Double) As Double
    AddTwo = value + 2
End Function
LVL 4
sublimationAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Move the function to the code module where it is being called (used) within the Visual Basic (for Applications) Project, & change the "Public" prefix to "Private", thus:

Private Function AddTwo (value As Double) As Double
    AddTwo = value + 2
End Function
0
sublimationAuthor Commented:
Hi, Fanpages

Excel 2010... The function currently resides in a module but the module does not have a public or private scope setting.
0
sublimationAuthor Commented:
Hi, Fanpages

I tried that.  The function still shows from sheet...
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

[ fanpages ]IT Services ConsultantCommented:
If the prefix to the Function is changed to Private (in a Public code module) then you will not be able to see it as a recognised function within the in-cell edit of a worksheet (although you will still be able to use it, if you know what the function is called).

However, did you wish to make the function completely "invisible"; unable to be used?

If so, move the definition of the Function to the Worksheet's code module (& keep it as a Private definition).
0
sublimationAuthor Commented:
Hi, fanpages

Yes, I want it NOT to be available for a sheet UDF but available for the VBA...
0
[ fanpages ]IT Services ConsultantCommented:
Then, as I said, move the function to the respective Worksheet's code module, & change the definition (of the Function) to Private scope.

If you then attempt to use AddTwo(...) within a cell, you will see a result of #NAME? but the function can still be used within Visual Basic for Applications code within the same code module.
0
sublimationAuthor Commented:
fanpages

I have tried that... Have you?  The function is still able to be called.
Example.jpg
0
[ fanpages ]IT Services ConsultantCommented:
Yes, I have tried what I suggested.

You still have the code within "Module1".

Please move it to the Worksheet code module; "Sheet1".
0
sublimationAuthor Commented:
HI, fanpages.

Thanks that actually works now.

How would I then call it from my VBA code in Module1 if its Private in Sheet1..?
0
[ fanpages ]IT Services ConsultantCommented:
Also place the code that calls the Private function within Sheet1's code module, or change the scope of the function from Private to Public.
0
sublimationAuthor Commented:
HI, fanpages.

You have lost me know.  What is the point of putting the code as private in the sheet1 code module if it now can't be used at all.  Are you able to send me an example?
0
[ fanpages ]IT Services ConsultantCommented:
The point is to hide it from all but local functions of the (Private) worksheet code module in which it resides, or from fully-qualified usage in Public code modules, or so I thought from your question.

Please see an attached example.

Contents of the "Sheet1" code module:

Option Explicit
Public Function AddTwo(ByVal dblValue As Double) As Double

  AddTwo = dblValue + 2#
    
End Function

Open in new window



Contents of the "Module1" code module:

Option Explicit
Public Sub Q_28708072()

  MsgBox Sheet1.AddTwo(10), _
         vbInformation Or vbOKOnly, _
         ThisWorkbook.Name
         
End Sub

Open in new window



Usage within the [Sheet1] worksheet:

AddTwo in [Sheet1]Q-28708072.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sublimationAuthor Commented:
Thanks,

So all my functions would have to reside in a sheet's code module to prevent them from being called as sheet UDFs?
0
[ fanpages ]IT Services ConsultantCommented:
You could store all the functions you wish to 'hide' from worksheet in-cell usage within the Workbook code module (default name of "ThisWorkbook"), rather than in a specific Worksheet code module (or modules).
0
sublimationAuthor Commented:
Could we use classes in a way, making some functions in the class private/public?  Then call the UDF function with class qualifier... Somebody told me this, they may be wrong though
0
[ fanpages ]IT Services ConsultantCommented:
The use of a Class module may not be suited to your project/application.

Without a full explanation of the intention of your code, I would not be able to advise further.

However, I would suggest that this is outside the scope of the original question.
0
Martin LissOlder than dirtCommented:
fanpages may have provided you with the answer you need but if not then it would help to see your actual workbook so could you post it?

In any case I can't reproduce your problem so please tell me what's different in your workbook. I created a workbook with code in the sheet and code in Module1 as shown here.
codeWhen I start to type in the formula all that I see is this.
 sheetAnd note that when I want to use AddTwo somewhere outside of Sheet1 then it's necessary to prefix the subs name with the name of the sheet as shown in the "test" sub.
0
[ fanpages ]IT Services ConsultantCommented:
In any case I can't reproduce your problem...

Martin: The "problem", if there was one, was resolved by Comment ID: 40943859.

sublimation had not moved the code to the Worksheet ("Sheet1") code module as advised; it was still in the Public code module ("Module1") until then.
0
sublimationAuthor Commented:
Sorry, Martin.

I didn't see your comment.  Thanks for your efforts.  

I guess I wanted to know if there was a way in excel to make a function in a module unavailable (not invisible as I could type the name even if I do not see it in the function list) for sheet UDF.  But it seems that there isn't.

Cheers.

Ed
0
[ fanpages ]IT Services ConsultantCommented:
Expanding upon "unavailable" (rather than "invisible"); you could force one of the parameters to be a particular value that is near-impossible to guess/pass from usage within an in-cell function, but could easily be determined as being passed from a call/execution/reference from a Visual Basic for Applications statement.
0
sublimationAuthor Commented:
Hi, Fanpages.

What I ultimately wanted to be able to do was have my functions in a single module but have some of those functions not be able to be used as a UDF,  I guess it was my expectation that threw me.
0
Martin LissOlder than dirtCommented:
Here's another way to handle it.

Public Function AddTwo(value As Double) As Double
    If TypeOf Application.Caller Is Excel.Range Then
        MsgBox "AddTwo may not be used as a formula"
        AddTwo = CVErr(xlErrNA)
        Exit Function
    End If
    AddTwo = value + 2
    
End Function

Open in new window

1
sublimationAuthor Commented:
Cheers, Martin.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help in any way.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.