Convert VBA Array from Boolean to Integer

Dim b As Boolean: b = True
Debug.Print CInt(b)

Shows -1 in the immediate window. So if you use CInt(Put Boolean array here) I would think it would convert.

This kind of works but I'm not sure how you would use it.

Dim arrBool
Dim arrInt

    arrBool = Array(True, False, True, True, False, False)
    arrInt = Evaluate("--({" & Join(arrBool, ",") & "})")

Get the Join part, just cannot get everything else to speak to me, working........

Thanks shg! didn't realize the 2D requirement. Stupid question, Loading range from sheet without looping automatically creates 2D? I thought I could get a nice 1D that way. Used Transpose to get a 1D, but a little baffled

Holy beez wax, this is the most exacting piece of code I've worked with so far. I know I'm obviously missing some things:

arrBool = Array(True, True, False, False, True)
Bloh = Evaluate("--({" & Join(arrBool, ",") & "}" & "=TRUE)")

I would create UDF before think that way.

Sub test()
    Dim a
    With Cells(1).Resize(30, 5)
        .Formula = "=randbetween(5,10)"  '<- generate data
        a = .Value
    End With
    MsgBox SumIfsArray(a, 1, 2, 10, 5, 8)
End Sub

Function SumIfsArray(a, SumCol, ParamArray Criteria()) As Double
    Dim i As Long, ii As Long, flg As Boolean
    For i = LBound(a, 1) To UBound(a, 1)
        For ii = LBound(Criteria) To UBound(Criteria) Step 2
            If a(i, Criteria(ii)) <> Criteria(ii + 1) Then flg = True: Exit For
        Next
        If Not flg Then SumIfsArray = SumIfsArray + Val(a(i, SumCol))
        flg = False
    Next
End Function

OOps, CountIfs...

Sub test()
    Dim a
    With Cells(1).Resize(30, 5)
        .Formula = "=randbetween(5,10)"  '<- generate data
        a = .Value
    End With
    MsgBox CountIfsArray(a, 2, 10, 5, 8)
End Sub

Function CountIfsArray(a, ParamArray Criteria()) As Long
    Dim i As Long, ii As Long, flg As Boolean
    For i = LBound(a, 1) To UBound(a, 1)
        For ii = LBound(Criteria) To UBound(Criteria) Step 2
            If a(i, Criteria(ii)) <> Criteria(ii + 1) Then flg = True: Exit For
        Next
        If Not flg Then CountIfsArray = CountIfsArray + 1
        flg = False
    Next
End Function

Dim arrBool
Dim arrInt(5) As Integer
Dim i As Integer
    arrBool = Array(True, False, True, True, False, False)
    For i = LBound(arrBool) To UBound(arrBool)
        arrInt(i) = CInt(arrBool(i))
    Next i

Only slightly different from Norie's suggestion.

Why are you trying to do everything in VBA and not take advantage of Excel's built-in functionality?

For example, if you wanted to do a simple, straight down the line, no quirks sum, why not use Excel's SUM worksheet function.

If you really need it in code then use WorksheetFunction or Evaluate.

'data hogs'?

1. The string argument to the Evaluate function is evaluated by Excel's formula evaluator, not VBA, and

2. Your arrBool array is 2D, and Join only accepts a 1D array. 1-based, 0-based, or anything else-based doesn't matter.

    Dim avb As Variant
    
    avb = Array(True, True, False, False, True)
    MsgBox Evaluate("sum(--{" & Join(avb, ",") & "})")

I know you don't want to hear it, but you're wasting your time if the goal is code efficiency. Evaluate is slow.

The brackets are just a shortcut to the evaluate function.

Worksheet.Evaluate controls the context (the worksheet for unqualified range references) in which the formula is evaluated; I doubt there's any difference between that and Application.Evaluate in execution time.

cmore

What is it you are trying to get to work?

Sorry took so long to respond.

So things are workingish, I'm just trying to find easier ways to create some of the sub-arrays needed and easier way for counts. For instance. There are a few formulas on the Summary Tab I haven't automated yet, and then there's info. that populates on the Data tab, based on the Summary Info. that's honestly kind of sluggish right now and finally, I am going to need to populate some chart data.

In Excel going from boolean to to integers makes things a lot more flexible, please let me know if I'm off base