Excel VBA：神秘零被添加到使用“ For”创建的数组中循环 [英] Excel VBA: Mysterious Zero is Being Added to Array Created with "For" Loop

问题描述

大家好：我正在尝试制作一个函数，该函数会在数组中挖掘并在range1中的对应值等于criteria1时添加range2中的值。

Hey everyone: I'm trying to make a function that digs through an array and adds values from range2 when the corresponding value from range1 equals criteria1.

m对VBA来说相对较新，因此它不是世界上最优雅的函数，但这是我的代码：

I'm relatively new to VBA, so it's not the most elegant function in the world, but here's my code:

Function SingleArray(range1 As Range, range2 As Range, criteria1 As String)

Dim newrange() As Double
Dim d As Integer
Dim g As Integer
Dim i As Integer

g = Application.WorksheetFunction.CountIf(range1, criteria1)

ReDim newrange(g)

d = 1

For i = 0 To (range1.Count)
    If range1(i) = criteria1 Then
        newrange(d) = range2.Item(i).Value
        d = d + 1
        End If
    Next i


SingleArray = newrange

End Function

这是我的数据样本：

range2  range1
-5000   Bob
-5000   Jim 
 200    Bob 
 500    Jim 
 5000   Bob 
 200    Bob 
 300    Bob 
 1000   Bob

当我将条件设置为 Bob时，返回的数组如下：

When I set the criteria as "Bob," the array that is returned is as follows:

{0,-5000,200,5000,200,300,1000}

我是真的对于零在其中如何进入感到困惑。

I'm genuinely at a loss for how that zero is making it in there. Any thoughts you can provide would be most welcome!

推荐答案

1-D数组默认为基于零的索引结构（例如 0、1、2、3 .... ）。您正在使用基于一个索引（例如 1、2、3、4 ... ）遍历范围。

1-D arrays default to a zero-based index structure (e.g. 0, 1, 2, 3, ....). You are looping through the ranges with a one based index (e.g. 1, 2, 3, 4, ...).

声明 ReDim newrange（5）您实际上是在创建一个包含六个元素而不是五个元素的数组（例如， 0、1、2、3、4、5 ）

When you declare ReDim newrange(5) you are actually creating an array with six elements, not five (e.g. 0, 1, 2, 3, 4, 5)

通过将此编译器指令放在代码表的顶部，可以使该代码表上的所有数组默认为基于一个的索引。

You can make all arrays on that code sheet default to a one based index by putting this compiler directive at the top of the code sheet.

Option Base 1

您还可以通过指定下边界和上边界来更改动态声明数组的方式。

You can also change the way the array is declared on the fly by specifying the Lower Boundary and the Upper Boundary.

ReDim newrange(1 to g)

这篇关于Excel VBA：神秘零被添加到使用“ For”创建的数组中循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！