Function with constant as an argument

Question

Some predefined VBA methods and functions require that a specific constant be passed to them, e.g.:

Application.Calculation = xlCalculationAutomatic
.cells(i,j).End(xlUp).Row
.PpParagraphAlignment = ppAlignCenter

In these snippets, the constants are xlCalculationAutomatic, xlUp and ppAlignCenter.

When the function/method is called and one is asked to populate the argument, VBE Intellisense is typically offering a dropdown of the valid constants to select from.

Is there a way to achieve the same thing with my own subroutines and functions? E.g. in the below case, the argument "sRowOrCol" requires the user currently to type in the literals "Row" or "Col", however I would like to offer the user a dropdown containing e.g. "xlRow" and "xlCol".

Function FindLast(ws As Worksheet, sRowOrCol As String, iColRow As Long)
    If sRowOrCol = "Row" Then
        FindLast = ws.Cells(ws.Rows.Count, iColRow).End(xlUp).Row
        Exit Function
    ElseIf sRowOrCol = "Col" Then
        FindLast = ws.Cells(iColRow, ws.Columns.Count).End(xlToLeft).Column
        Exit Function
    Else
        MsgBox "Invalid argument"
    End If    
End Function

Answer 1

It seems like you’re looking for the Enum statement. In your case, it could look like this:

Enum Margin
    Row
    Column
End Enum

' …

Function FindLast(ws As Worksheet, margin As Margin, iColRow As Long)
    If margin = Row Then
    …
End Function

IntelliSense will work with this but you might want to give your enum constants a common prefix (e.g. mar) to facilitate their selection in the IntelliSense DropDown box. That’s why e.g. xlUp has the prefix xl. Though personally I’m not a big fan of such prefixes.