How to define structure in VBA? Alternative to Delphi "record" command

Question

I am Newbie to VBA. I would like to define my own structure, just like in Delphi is something like:

type Name = Record
    Declarations ...
end; 

For example in Delphi I would write:

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;

So I have declared the type or structure TNUMBER, which contains various members. Then I created array of this type and I process numbers and change the records referred by variable my_numbers. So I need to create similar structure for VBA to count numbers obtained from Excel cells. The purpose of this structure is to calculate if the numbers are too close each other or if they are far enough to (decide whether to) draw a point or circle on my chart drawings.

Answer 1

You could rewrite your example in VBA like this:

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

and then you would of course execute the test method.

Also, you would not want to use:
For i = 0 To 74
but instead you would use:
For i = LBound(my_numbers) To UBound(my_numbers)
so that if the array size changes the code still runs.