0

I have created a custom function that I am using excplicitly in another module in VBA. Function looks something like this:

Function Coverts(ByVal inputString As String) As String
   'Coverts code here
End Function

It works perfectly fine in both VBA and Excel UI. However, I do not want it to work or appear in Excel UI as I only want to use it in VBA. Is there a way to do this?

Thanks

Oday Salim
  • 1,129
  • 3
  • 23
  • 46
  • 1
    Possible duplicate of [Making sure public VBA methods don't show up in the list of Excel macros](https://stackoverflow.com/questions/296696/making-sure-public-vba-methods-dont-show-up-in-the-list-of-excel-macros) – Pm Duda Aug 29 '18 at 08:21

3 Answers3

4

Put

Option Private Module

at the top of the module containing your function.

From MSDN:

When a module contains Option Private Module, the public parts, for example, variables, objects, and user-defined types declared at module level, are still available within the project containing the module, but they are not available to other applications or projects.

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70
SJR
  • 22,986
  • 6
  • 18
  • 26
1

you can add the keywords Public or Private to your functions, subs or global variable to have that specified.

so if you want to only want this function to be accessible by your code and not in excel sheets you can add private:

Private Function Coverts(ByVal inputString As String) As String
   'Coverts code here
End Function
DaanV
  • 339
  • 1
  • 6
  • 1
    If I do that, then I will not be able to access this function in another module. Correct? – Oday Salim Aug 29 '18 at 08:04
  • 1
    Correct. Check comment under your question. – Pm Duda Aug 29 '18 at 08:06
  • 1
    While this does limit the scope to `Private` I do have a feeling, that OP's intention is to access the function's scope in his other functions, just not outside of the module. But well, since it is not specified, this is technically a correct answer – Samuel Hulla Aug 29 '18 at 08:18
1

You can make the function inoperable if called from the worksheet by identifying the Application.Caller. If called as a function from the XL UI, this will be Range (i.e. the cell the function is within).

Function Coverts(ByVal inputString As String) As String

   If TypeName(Application.Caller) = "Range" then
      Coverts = cverr(xlerrna)
      exit function
   end if

   'Coverts code here

End Function