Excel/VBA：将单个单元格作为参数传递
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14479627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel/VBA: Passing a single cell as argument
提问by user776686
I want to make my custom VBA function accept only a single-cell argument. What is the rightway of doing it:
我想让我的自定义 VBA 函数只接受单单元格参数。正确的做法是什么：
- pass
myCell as Cell
- 经过
myCell as Cell
or:
或者：
- pass
myRange as Rangeand get (how?) the left-upper cell by default?
myRange as Range默认情况下传递并获取（如何？）左上角的单元格？
回答by InContext
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
回答by bonCodigo
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
回答by user776686
topLeftValue = myRange.Cells(1, 1).Value
回答by Brett Rosequist
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.
我在我自己的函数/子程序中添加了以下范围检查，以便即使传递了 > 1 个单元格的范围也能继续。如果将具有多个单元格的区域传递给函数，则这会强制选择最左上角的单元格。这是其他答案的替代路径，它结束当前功能。
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
回答by PerR
numRow = myCell.Columns.Count
numCol = myCell.Rows.Count
Should be
应该
numColum = myCell.Columns.Count
numRow = myCell.Rows.Count
