Take augmented function inputs

Question

I have the following declared:

Public Declare Function GetSystemMetrics Lib "user32.dll" (ByVal X0_Y1 As Long) As Long

It grabs the monitor resolution.

So that in the future I remember to type 0 for x resolution and 1 for y resolution I have named the argument variable to illustrate that (X0_Y1). (So user can use ctrl+a or ctrl+Shift+a when entering the function to display its arguments)

But what I really want is to type "x" to get the x res and "y" for y res (i.e. =GetSystemMetrics("x") gives the x resolution). Is there a way to do this within the function decleration? Like (ByVal iif(X0_Y1 ="x",0,1) As Long) to specify what to do with the input.

I'd rather not just do this:

Function GetRes(letter As String) As Long
    Dim i As Long
    i = IIf(letter = "x", 0, 1)
    GetRes = GetSystemMetrics(i)
End Function

As it involves creating a whole new function which is more unweildy than just using the base one. Perhaps there's some way to specify x/y as constants so that if the user enters them they are read as numbers not strings? Another nice option would be to get the input options displayed like the Cell function does. (Similar to this question, but not the same)

Pᴇʜ · Accepted Answer · 2017-05-02T13:07:17.593

You can use an Enum Statement for this.

Declare an enum and your function like that

Public Enum MetricsType
    xMetrics = 0
    yMetrics = 1
End Enum

Public Declare Function GetSystemMetrics Lib "user32.dll" (ByVal xy As MetricsType) As Long

and you can use it like this

Dim x As Long, y As Long
x = GetSystemMetrics(xMetrics)
y = GetSystemMetrics(yMetrics)

This will also enable AutoComplete in the VBA editor.

To enhance the usability within the worksheet you can register/unregister your function as a UDF (user defined function). After registering you can select your function from the function menu and you see the comments within this dialog.

Sub RegisterUDF()
    Dim s As String
    s = "Some description here" & vbLf _
    & "GetSystemMetrics(<Metrics>)"

    Application.MacroOptions Macro:="GetSystemMetrics", Description:=s, Category:="My Category"
End Sub

Sub UnregisterUDF()
    Application.MacroOptions Macro:="GetSystemMetrics", Description:=Empty, Category:=Empty
End Sub

To get the enum working within the worksheet there is only workaround possible. Therefore you add a named range referring to =0 or =1 like below:

Sub RegisterEnum()
    ActiveWorkbook.Names.Add Name:="xMetrics", RefersToR1C1:="=0"
    ActiveWorkbook.Names.Add Name:="yMetrics", RefersToR1C1:="=1"
    'NOTE: don't use x or y as names here as these refer to the column names. 
    'That's why I used xMetrics instead.
End Sub

Then you are able to use the function in your worksheet like =GetSystemMetrics(xMetrics).

Creating the exactly same behavior like the built-in functions isn't possible as far as I know.

This is working when called with a `Sub` as you note, but not when called from the worksheet itself (as a cell formula). Is there a way of doing this? — Greedo, May 02 '17 at 12:37
As far as I know there is only a workaround like I added in the answer above. So you get a similar functionality but not exactly the same. — Pᴇʜ, May 02 '17 at 13:00

Take augmented function inputs

1 Answers1