User Defined Function arguments help

PRyankid

Board Regular
Joined
Aug 23, 2005
Messages
144
I have created a function using VBA and it works fine in my spreadsheets. The only thing that I can't get to work is how to display the function argument popup box that appears below the function bar (i.e. when you type "=Sum(" into the bar, a popup box appears showing "SUM(number1, [number2], ...)". Does anyone know how to get this popup box to show up for a user defined function? Thanks!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
I don't see why that wouldn't be displayed.

It works for me.:eek:

Where have you put the code for the UDF?

Does it appear in the User Defined category when you goto Insert>Function...?
 

PRyankid

Board Regular
Joined
Aug 23, 2005
Messages
144
The functions do show up in the User Defined category under Insert>Function. I have defined them in a module within a normal module (not a Class module). Here is an example of one of the functions I have (It converts pressures from one set of units to another):
Code:
Function SimpPress(P As Double, InputP As String, OutputP As String)
    ' This first converts pressure to MPa and then back to whatever
    ' It currently accepts as types: MPa, Pa, psia, psig, in_H2O
    
    If UCase(InputP) = UCase(OutputP) Then
        SimpPress = P: Exit Function
    Else
        Dim Mult1 As Double, Const1 As Double, Mult2 As Double, Const2 As Double
        Mult1 = 1: Const1 = 0: Mult2 = 1: Const2 = 0
        Select Case UCase(InputP)
            Case "MPA"
            Case "PA": Mult1 = 0.000001
            Case "PSIA": Mult1 = 0.00689475729 '1 pound per square inch = 0.00689475729 megapascals
            Case "PSIG": Mult1 = 0.00689475729: Const1 = 0.101325
            Case "IN_H2O": Mult1 = 0.00024908891 '1 inch of water = 0.00024908891 megapascals
            Case Else: SimpPress = InputP & "?": Exit Function
        End Select
        Select Case UCase(OutputP)
            Case "MPA"
            Case "PA": Mult2 = 1000000
            Case "PSIA": Mult2 = 145.037738 '1 megapascal = 145.037738 pounds per square inch
            Case "PSIG": Mult2 = 145.037738: Const2 = -14.6959488
            Case "IN_H2O": Mult2 = 4014.63076 '1 megapascal = 4014.63076 inches of water (1/0.00024908891)
            Case Else: SimpPress = OutputP & "?": Exit Function
        End Select
        SimpPress = (P * Mult1 + Const1) * Mult2 + Const2
    End If
End Function
The end goal is that it display SIMPPRESS(P, InputP, OutputP) or SIMPPRESS(P As Double, InputP As String, OutputP As String) as a popup when I type "=SimpPress(" into the formula bar.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Again that works for me.

You are pressing the equal button aren't you?

As far as I know you need to do that to get the dropdown, even for built-in functions.
 

PRyankid

Board Regular
Joined
Aug 23, 2005
Messages
144

ADVERTISEMENT

I am putting in an equals sign, which baffles me. When I type in "=SimpPress(" nothing pops up, but if I then push Ctrl+Shift+A it will put in "P, InputP, OutputP)" next to it.

Still no popup however. I am using Excel 2002 with Windows XP.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Well I'm on 2000 and I have to click the equal sign next to the formula bar to get the popup.
 

PRyankid

Board Regular
Joined
Aug 23, 2005
Messages
144
OH, I'm sorry, I was confused. If I push the little fx next to the formula bar once I type it in I get a popup that has spaces for each of my variables.

I think popup was the wrong word choice. What I'd like to see once I start typing in the function "=SimpPress(" is that little yellow quick info bar that shows up below the formula bar. (the one that you could click on the function name if you wanted to and it would pull up the Help menu on that function)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,480
Messages
5,601,911
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top