Excel / VBA: passing one cell as argument

Question

I want my custom VBA function to accept only a singleton argument. What is it like:

• pass myCell as Cell

or

• pass myRange as Range and get (how?) the top left cell by default?

Answer 1

If you select more than one cell, the function will exit:

 Function AcceptOneCell(rng As Range) If (rng.Cells.Count > 1) Then AcceptOneCell = "Only allow 1 cell" Exit Function End If ' your code here End Function 

Answer 2

Assuming your user enters a range with multiple columns and rows, you can perform the following check to exit the function if that is what you had in mind in the question ...

 Function myFunction(ByRef myCell as Range) as SomeDataType_of_your_choice Dim numRow as Long, numCol as Long numRow = myCell.Columns.Count numCol = myCell.Rows.Count If numRow > 1 or numCol > 1 Then MsgBox "Only one cell is accepted" Exit Function Else '-- do other stuff you want to do here End If End Function 

Answer 3

 topLeftValue = myRange.Cells(1, 1).Value 

Answer 4

 numRow = myCell.Columns.Count numCol = myCell.Rows.Count 

Must be

 numColum = myCell.Columns.Count numRow = myCell.Rows.Count