将一系列单元格从Excel传递到VBA中的函数 [英] Passing a range of cells into a function in vba from excel

问题描述

我正在尝试创建一个函数，以便= processCells(A1:A10)接受单元格的范围，并允许我为每个项目添加10，并在单元格A11:A20中显示新的数字.我想在工作表中使用该功能，以便用户可以手动选择单元格A1:A10，因此他们可以选择B1:B10等...而不是A1:A10

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

我的问题是将工作表的单元格范围传递给函数，然后对其进行处理.

My problem is passing the range of cells for a worksheet to a function and then processing them.

推荐答案

以下示例UDF可帮助您入门

Here's a sample UDF to get you started

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

要按照说明使用它，请在单元格单元格 A11:A20

To use it as you describe, enter it as an Array Formula =processCells(A1:A10) in cells cells A11:A20

这篇关于将一系列单元格从Excel传递到VBA中的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！