在VBA函数中访问单个数组元素 [英] accessing individual array elements in VBA function

问题描述

VBA新手在这里.我正在尝试将数组(是静态的，但也请为动态范围提供答案)传递给函数.然后，将各个数组元素分配给唯一变量，然后在自定义公式中使用这些变量.我只是四处浏览并编写了代码，但一直得到#VALUE！错误.代码要点如下:

VBA newbie here. I am trying to pass an array (it is static, but please answer for dynamic range as well) to a function. Then assign individual array elements to unique variables and use these variables in a custom formula. I just browsed around and wrote the code but keep getting #VALUE! error. The gist of the code is below:

Public Function mytest(ByRef arr1 As Range)
Dim A As Double
Dim B As Double

A = arr1(0)
B = arr1(1)

mytest = A + B 'The actual formula is a bit more complicated than simple addition
End Function

我不确定我在做什么错.如果有人有解决方案，请您解释一下为什么也可以解决.感谢您能获得的所有帮助.非常感谢！

I am not sure what am i doing wrong at all. If anyone has a solution, can you please explain why it works as well. I appreciate any and all help I can get. Many thanks !

推荐答案

正如科尔曼指出，范围不是数组，请考虑:

As Coleman pointed out a range is not an array, consider:

Public Function mytest(ByRef arr1 As Range)
    Dim A As Double
    Dim B As Double

    A = arr1(1, 1)
    B = arr1(2, 1)

    mytest = A + B 'The actual formula is a bit more complicated than simple addition
End Function

注意:

• 我们将 Range 视为数组
• 这是二维的
• 它是基于 1 的
• 如果仅处理 Range's 的值，则可以在函数内创建一个内部数组，该数组直接映射到传递的 Range .
• 如果 Range 是真正动态的，(如溢出范围)，那么您只需传递锚定单元格即可.

• we treat the Range similar to an array
• it is two dimensional
• it is 1 based
• if you are only dealing with the Range's value, you could create an internal array within your function that directly maps to the passed Range.
• if the Range is truly dynamic, (like a Spill range) then all you need to pass is the anchor cell.

这篇关于在VBA函数中访问单个数组元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！