excel - 如何在 VBA 中定义结构？ Delphi "record"命令的替代方案

Question

我是VBA的新手。我想定义我自己的结构，就像在 Delphi 中是这样的:

type Name = Record
    Declarations ...
end; 

例如在德尔福我会写:

type TNUMBER = Record
    value: integer;
    min: integer;
    max: integer;
    count: byte;
    processed: boolean;
end; 

var my_numbers: array[0..74] of TNUMBER;
i: integer;
range: byte;
begin
range := 7;
for i:=0 to 74 do
  if not my_numbers[i].processed then
    begin
      my_numbers[i].value := round(somenumber);
      my_numbers[i].min := my_numbers[i].value-range;    
      my_numbers[i].max := my_numbers[i].value+range;
      my_numbers[i].count := 0;
    end
  else
    begin
      if (somenumber >= my_numbers[i].min) AND
         (somenumber <= my_numbers[i].max) then
        begin
          inc(my_numbers[i].count);  ' increase counter
        end;
    end;

所以我声明了类型或结构 TNUMBER ，其中包含各种成员。然后我创建了这种类型的数组并处理数字并更改变量 my_numbers 引用的记录.所以我需要为 VBA 创建类似的结构来计算从 Excel 单元格中获得的数字。这种结构的目的是计算数字是否彼此太近，或者它们是否足够远(决定是否)在我的图表上绘制一个点或圆。

Answer 1

您可以像这样在 VBA 中重写您的示例:

Option Explicit

Private Type TNUMBER 'Or Public if needed in another module
    value As Integer
    min As Integer
    max As Integer
    count As Byte
    processed As Boolean
End Type

Public Sub test()
    Dim my_numbers(0 To 74) As TNUMBER
    Dim i As Integer
    Dim range As Byte
    Dim somenumber As Double

    range = 7
    For i = 0 To 74
        If Not my_numbers(i).processed Then
            my_numbers(i).value = Round(somenumber)
            my_numbers(i).min = my_numbers(i).value - range
            my_numbers(i).max = my_numbers(i).value + range
            my_numbers(i).count = 0
        Else
            If (somenumber >= my_numbers(i).min) And _
               (somenumber <= my_numbers(i).max) Then
                my_numbers(i).count = my_numbers(i).count + 1
            End If
        End If
    Next i
End Sub

然后你当然会执行 test方法。
此外，您不想使用: For i = 0 To 74但你会使用: For i = LBound(my_numbers) To UBound(my_numbers)这样如果数组大小发生变化，代码仍然会运行。