Adding Row Values in an Array: VBA

Question

I'm building a Genetic Algorithm in VBA and my fitness function depends on how many 1's the array row has. The array is a combination of 1's and 0's. The Array can be any size (2D) and I need to add the number of 1s in the row and compare it to the rest of the values. I was considering finding the max value of 1s and comparing it the min values of 1 in the array and moving forward from there, but I'm not sure if this is the best way to do this.

If you guys could give me some tips on how to do this that would be great! Or if there are some array addition functions that I'm missing in VBA.

It's weird to program genetic algorithms in VBA, but give +1 anyway. — nalply
– nalply, Commented Sep 30, 2012 at 14:05

Rob Gale · Accepted Answer · 2012-09-29 18:00:15Z

1

Another approach would be to use Excel's SUM() function to do the work. Summing the array would give you the same answer as counting the ones, and you can use the Application.WorksheetFunction object to give you access to SUM():

x = Application.WorksheetFunction.Sum(aArray)

edited Sep 29, 2012 at 18:00

answered Sep 29, 2012 at 17:52

Rob Gale

3251 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve Rindsberg · Accepted Answer · 2012-09-29 16:10:29Z

0

You'd only need to count the ones; if each individual element isn't a 1, it's a zero, so you can subtract the number of 1s from the size of the array to get number of zeros.

Function CountTheOnes(aArray As Variant) As Long
    Dim x As Long
    Dim OnesCount As Long
    For x = LBound(aArray) To UBound(aArray)
        If aArray(x) = 1 Then
            OnesCount = OnesCount + 1
        End If
    Next
    CountTheOnes = OnesCount
End Function

' and to test it:

Sub TestIt()
    Dim aArray(1 To 10) As Byte
    Dim x As Long

    ' stuff the array with zeros
    For x = LBound(aArray) To UBound(aArray)
        aArray(x) = 0
    Next

    ' then add a couple of random 1s
    aArray(3) = 1
    aArray(7) = 1
    aArray(9) = 1


    x = CountTheOnes(aArray)

    Debug.Print "Ones:" & vbTab & x
    Debug.Print "Zeros:" & vbTab & UBound(aArray) - x

End Sub

answered Sep 29, 2012 at 16:10

Steve Rindsberg

14.9k1 gold badge32 silver badges37 bronze badges

1 Comment

Steve Rindsberg Over a year ago

If you're doing this in Excel, then I'd go with Rob G's suggestion, by all means.

Collectives™ on Stack Overflow

Adding Row Values in an Array: VBA

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related