Prevent VBA Functions from being available on worksheet

[jewelsharma]

Hi,
I use a few Functions in my VBA code. All these functions, are declared as "Public", and reside in a single module. However, they are called from many different modules during code execution. (i.e. many functions called from many modules - hence the "Public" declaration).

My issue is that in addition to being available to different VBA Modules, these Functions are also available on the worksheet as a UDF (so if a user presses "=" in the formula bar, the auto-complete shows these functions when the first characters match). Is there a way to remove the availability of the function on user worksheet? i.e. to allow a Function to be called from different modules in VBA, but prevent it from being available on the worksheet.

My current work-around is to prefix all Public Function names with letter "j" - as no excel formula seems to begin with it - none of them show up as auto-complete options. Nevertheless, the Functions are still available to the user - which is what I would like to prevent.

Thanks & regards.

[JBeaucaire]

Make them PRIVATE, that's the only reason to use private, to hide them from the user function list. Private functions are fully available to all code modules, make sure they are stored in code modules, not sheet modules or the ThisWorkbook module.

[jewelsharma]

I declared a Function as Private and then called it from a procedure in another module and received the following error (along with the Function name highlighted):

Sub or Function not Defined

(Both, the calling procedure as well as the Private Function are on different modules - but neither of them is on a Class Module, or Userform, or Workbook/Worksheet object such as ThisWorkBook).

Further, among the many reasons suggested by MS Excel Help regarding the error, one of them is:

Sub, Function, or Property not defined (Error 35)

The specified procedure isn't visible to the calling procedure.
Procedures declared Private in one module can't be called from procedures outside the module.

From the above, it would appear that if a Function is declared as "Private", it can only be called by other functions or procedures residing within the same module. Or did I miss something .

I've double checked all spellings etc... As a matter of fact, if I simply change the Function from "Private" back to "Public", the Procedure works fine.

[JBeaucaire]

Apologies, I never came across that before. I guess I've always built my projects fully into one module.

So, the next obvious fallback is to not use private function, but to use SUBs with parameters.

Outside of the routines you declare a public variable. Then in your main macro you call the subroutine, feeding in the parameters needed, making sure the result of that macro is the public variable is filled in, thus available for use back in the main macro when it's done.

[jewelsharma]

Yes, that would be a possible workaround. I was hoping there would be something at the declaration level itself, or some kind of toggle to allow/disallow VBA Functions as UDF.
Nevertheless, many thanks for the help. I can work with this.

[romperstomper]

You can just add

Please Login or Register  to view this content.

to the top of the module containing your functions. This will hide them from the function wizard and the function dropdown, although if the user knows the name of the function he can still type it into a cell.

[TMS]

If you make it a private module, won't that restrict its availability to the project in which it resides?

If I have understood correctly, the OP wants the functions to be generally available to all VBA projects but not appear in the worksheet function list. I guess, for example, he may have a common function to determine the last row of data. This would not be useful on a worksheet.

Regards, TMS

[TMS]

Sorry, RS. Just re-read the OP and I think I may have misunderstood the requirement. For functions, and subroutines, in a VBA project, I would normally declare all modules as Private with only, maybe, a top level calling routine as normal. Or, if the routines were called from a form or command button, everything would be in private modules.

Regards, TMS

[jewelsharma]

romperstomper , TMS,
Option Private Module doesn't seem much more helpful than my original choice of starting a function name with letter "j" - in that it prevents the Functions from showing up via auto-complete, but do not specifically restrict the user from calling them from a workbook as a UDF.

I was hoping that there was some functionality built into Excel/VBA (maybe something like a Private Module can still be called via Module_Name.Function_Name from a procedure residing in another Module, but otherwise keeping it unavailable for the workbook)

Nevertheless, many thanks for taking time out to respond to this one.
Cheers!

[romperstomper]

Option Private Module has the added benefit that the functions are not listed in the function wizard. So a user has to know what they are called to actually use them in a sheet. There isn't really any way to prevent that besides perhaps adding code to each one to ensure that application.caller is not a range.