Excel/VBA: Passing a single cell as argument

Question

I want to make my custom VBA function accept only a single-cell argument. What is the rightway of doing it:

pass myCell as Cell

or:

pass myRange as Range and get (how?) the left-upper cell by default?

Can you show the function as well.. you can add in Myfunction(ByRef myCell as Range) as the parameter — bonCodigo
– bonCodigo, Commented Jan 23, 2013 at 12:26
Actually, as if often happens, solution comes up after asking the question: topLeftValue = myRange.Cells(1, 1).Value — user776686
– user776686, Commented Jan 23, 2013 at 12:28
is the following answer's validation that you are looking for? — bonCodigo
– bonCodigo, Commented Jan 23, 2013 at 12:32
I was searching for a swifty way for setting a default value. My above comment contains an answer that works best for me. Will add it as my own answer. Thanks anyway for the effort. But BTW: what's the purpose of ByRef - you meant to alter the param? — user776686
– user776686, Commented Jan 23, 2013 at 12:36
You don't provide the definition of topLeftValue though. It's best always to show your code instaed of a line. I missed out the default part though, if you had emphasized that you want to take first cell of any range then yes, would have been clearer. — bonCodigo
– bonCodigo, Commented Jan 23, 2013 at 12:39

InContext · Accepted Answer · 2013-01-23 12:51:39Z

25

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

answered Jan 23, 2013 at 12:51

InContext

2,52116 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BenKoshy Over a year ago

so can i call this function like this----> AcceptOneCell( Cells(1,1)) (will that work?

InContext Over a year ago

yes you can. Good also to define what sheet the cell (Range) is coming from: Sheet1.Cells(1,1) for example.

bonCodigo · Accepted Answer · 2013-01-23 12:28:53Z

11

Assuming your user will enter a range with multiple columns and rows, you can do the following check to exit the function if that's what you meant 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

answered Jan 23, 2013 at 12:28

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

1 Comment

Siddharth Rout Over a year ago

+ 1 for giving what the OP asked

user776686 · Accepted Answer · 2013-01-23 12:36:37Z

4

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

answered Jan 23, 2013 at 12:36

user776686

8,79817 gold badges80 silver badges139 bronze badges

Comments

Brett Rosequist · Accepted Answer · 2018-10-24 18:51:44Z

1

I've added the below range checks within my own functions/subs, in order to proceed even if a range of > 1 cell is passed. This forces the selection of the top-left-most cell in the case a range with multiple cells is passed to the function. This is an alternative path to the other answers, which end the current function instead.

Sub DoSomethingWithCells(StartCell As Range)
'Ensure that only the first cell of range is selected

If (StartCell.Cells.Count > 1) Then
    'Select only the first cell
    StartCell = StartCell.Cells(1, 1).Address
End If

'Do whatever you need to do here

End Sub

answered Oct 24, 2018 at 18:51

Brett Rosequist

113 bronze badges

Comments

Nazik · Accepted Answer · 2015-03-12 09:32:56Z

0

numRow = myCell.Columns.Count 

numCol = myCell.Rows.Count

Should be

numColum = myCell.Columns.Count 

numRow = myCell.Rows.Count

edited Mar 12, 2015 at 9:32

Nazik

8,45027 gold badges80 silver badges126 bronze badges

answered Mar 12, 2015 at 9:11

PerR

311 silver badge3 bronze badges

Collectives™ on Stack Overflow

Excel/VBA: Passing a single cell as argument

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related