Excel User Defined Functions hiding

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

Hi, Fanpages

Excel 2010... The function currently resides in a module but the module does not have a public or private scope setting.

Hi, Fanpages

I tried that.  The function still shows from sheet...

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).

Hi, fanpages

Yes, I want it NOT to be available for a sheet UDF but available for the VBA...

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.

fanpages

I have tried that... Have you?  The function is still able to be called.
Example.jpg

Yes, I have tried what I suggested.

You still have the code within "Module1".

Please move it to the Worksheet code module; "Sheet1".

HI, fanpages.

Thanks that actually works now.

How would I then call it from my VBA code in Module1 if its Private in Sheet1..?

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.

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?

Thanks,

So all my functions would have to reside in a sheet's code module to prevent them from being called as sheet UDFs?

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).

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

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.

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.
When I start to type in the formula all that I see is this.
 And 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.

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.

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

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.

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.

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

Select allOpen in new window

Cheers, Martin.

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