Public Function Not Showing Up in Spreadsheet

**PJ11** · 11-17-2011, 02:27 PM

Hi all,

I am pretty new to VBA and am having a problem that is driving me crazy with a public function that I tried to create.

I have a spreadsheet that has several custom public functions (some created by me, some created by other authors). The problem that I am having is that one of the public functions is not showing up when I start to type it in the spreadsheet (i.e., it is not showing up like =sum, or =product are). All the other functions that were created are showing up fine.

The other weird thing about this though, is that I also made another function that references the "missing" function in the VBA. This new function works fine, and seems to be using the "missing" function fine as well.

Is there something that I am missing? I feel like there must be a simple solution to this problem. Below is the code for the "missing" function.

Public Function StrtWeir(Length As Double, flow As Double) As Double
       StrtWeir = ((flow) / (2.152 * Length)) ^ (2 / 3)
End Function

Thanks for any help and let me know if you need anymore information.

**Whizbang** · 11-17-2011, 02:39 PM

Is it in a worksheet or ThisWorkbook module? If so, move it to its own module, or into the module containing the other functions.

**PJ11** · 11-17-2011, 03:07 PM

It is located in a module that contains other public functions that work fine (Including the one that references it).

**Whizbang** · 11-17-2011, 03:30 PM

Is the function/sub directly above it ended appropriately? When you go to "Debug" -> "Compile Project" in the VBE, do you get any errors?

**PJ11** · 11-17-2011, 05:42 PM

No errors in the code. The function directly above it seems fine (and works fine in the spreadshet). I included the first and last lines of the code for this module below:

There are a lot of macros/functions that have been built into this spreadsheet by other people, and the problem may be coming from one of them (although I don't see how they could affect the function). Since I don't think I would be able to post the spreadsheet, this may just have to remain an unsolved mystery unless there are any other potential problems anyone sees.

Option Explicit
Public Function WeirFlow(Length As Double, Hf As Double) As Double
End Function
Public Function QQf(H2 As Double, H1 As Double) As Double
End Function
Public Function SubWeir(Length As Double, flow As Double, H2 As Double) As Double
End Function
Public Function StrtWeir(Length As Double, flow As Double) As Double
End Function

Thanks

**Whizbang** · 11-17-2011, 05:51 PM

Try splitting up the functions into 2 or more modules. Maybe there is a limit to the number of UDFs allowed per module or workbook.

**PJ11** · 11-17-2011, 06:01 PM

Got it. When I put it into a new module, I realized that there was another module with functions that already had this function in it. So the name of the function must have been ambiguous.

Thanks a lot for your help!