CODEWITHSUNDEEP
vbaparameter-passingconstantsintellisense
Eleshar
Eleshar

Reputation: 513

Function with constant as an argument

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

Upvotes: 3

Views: 534

Answers (2)

EvR
EvR

Reputation: 3498

In this case you could also use the Excel-Enum XlRowCol:

It looks like you're also missing 'or' in If sRow__Col = "Row" Then

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

initially I only changed the part which had to be changed, to make it easier for OP; completed adjusted code:

Function FindLast(ws As Worksheet, RowOrCol As XlRowCol, ColRow As Long) As Long
    Select Case RowOrCol
        Case xlRows: FindLast = ws.Cells(ws.Rows.Count, ColRow).End(xlUp).Row
        Case xlColumns: FindLast = ws.Cells(ColRow, ws.Columns.Count).End(xlToLeft).Column
        Case Else: MsgBox "Invalid RowOrCol argument"
    End Select
End Function

Upvotes: 0

Konrad Rudolph
Konrad Rudolph

Reputation: 545618

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.

Upvotes: 4

Related Questions