vba 将一系列单元格从excel传递到vba中的函数中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8662569/
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
Passing a range of cells into a function in vba from excel
提问by Michaelb88
I'm trying to create a function so that =processCells(A1:A10) will take the range of cells and allow me to add 10 to each item and display the new numbers in cells A11:A20. I want to use the function in the worksheet so the user can select the cells A1:A10 manually so therefore they could select B1:B10 etc... instead of A1:A10
我正在尝试创建一个函数，以便 =processCells(A1:A10) 将采用单元格范围并允许我为每个项目添加 10 并在单元格 A11:A20 中显示新数字。我想在工作表中使用该函数，以便用户可以手动选择单元格 A1:A10，因此他们可以选择 B1:B10 等...而不是 A1:A10
My problem is passing the range of cells for a worksheet to a function and then processing them.
我的问题是将工作表的单元格范围传递给函数，然后处理它们。
回答by chris neilsen
Here's a sample UDF to get you started
这是一个示例 UDF，可帮助您入门
Function processCells(rng As Variant, Optional AddValue = 10) As Variant
Dim v As Variant
Dim i As Long, j As Long
Select Case TypeName(Application.Caller)
Case "Range"
' Called from a Formula
v = rng
If IsArray(v) Then
' Called from an Array Formula
For j = 1 To UBound(v, 1)
For i = 1 To UBound(v, 2)
If IsNumeric(v(j, i)) Then
v(j, i) = v(j, i) + AddValue
End If
Next i, j
Else
' Called from a single Cell
If IsNumeric(v) Then
v = v + AddValue
End If
End If
processCells = v
Case Else
processCells = vbEmpty
End Select
End Function
To use it as you describe, enter it as an Array Formula =processCells(A1:A10)in cells cells A11:A20
要按照您的描述使用它，请=processCells(A1:A10)在单元格单元格中将其作为数组公式输入A11:A20
