How to output Array Formula Function (Ctrl Shift-Enter) in Immediate Window using a test Sub?

[Juggler_IN]

How to test and output a Function (Array Formula output with Ctrl Shift-Enter in Worksheet) in Immediate Window using a test Sub?

 

[Crystalyzer]

Set the Cell with the formula to a parameter in your code and step to the point after the parameter is populated then either type "?" and the parameter name in the immediate window or Debug.Print parameter name in the code and step through that.

 

[Marcelo Branco]

An example - formula to count unique values in A2:A5

Sub aTest()
    Dim myVar As Long
   
    myVar = Evaluate("=SUM(IF(FREQUENCY(MATCH(A2:A5,A2:A5,0),ROW(A2:A5)-ROW(A2)+1),1))")
    Debug.Print myVar
End Sub

M.

 

[Juggler_IN]

If I have the following function, how do I test an array using a Sub in IW?

Function BINARY_SORT_FUNC(ByRef DATA_RNG As Variant, _
Optional ByRef ACOLUMN As Long = 1, _
Optional ByRef VERSION As Integer = 1)

Dim i As Long
Dim j As Long
Dim k As Long

Dim ii As Long
Dim jj As Long
Dim kk As Long

Dim SROW As Long
Dim NROWS As Long

Dim SCOLUMN As Long
Dim NCOLUMNS As Long

Dim DATA_MATRIX As Variant
Dim EXCHANGE_FLAG As Boolean

On Error GoTo ERROR_LABEL

DATA_MATRIX = DATA_RNG

If VERSION <> 1 Then: VERSION = 0 ' Descending

SROW = LBound(DATA_MATRIX, 1)
NROWS = UBound(DATA_MATRIX, 1)
SCOLUMN = LBound(DATA_MATRIX, 2)
NCOLUMNS = UBound(DATA_MATRIX, 2)
If ACOLUMN = 0 Then ACOLUMN = SCOLUMN

Do
    EXCHANGE_FLAG = False
    For i = SROW To NROWS Step 2
        k = i + 1
        If k > NROWS Then Exit For
        ii = DATA_MATRIX(i, ACOLUMN)
        jj = DATA_MATRIX(k, ACOLUMN)
        If (ii > jj And VERSION = 1) Or _
           (ii < jj And VERSION = 0) Then
            'swap rows
            For j = SCOLUMN To NCOLUMNS
                kk = DATA_MATRIX(k, j)
                DATA_MATRIX(k, j) = DATA_MATRIX(i, j)
                DATA_MATRIX(i, j) = kk
            Next j
            EXCHANGE_FLAG = True
        End If
    Next i
    If SROW = LBound(DATA_MATRIX, SCOLUMN) Then
          SROW = LBound(DATA_MATRIX, SCOLUMN) + 1
    Else
          SROW = LBound(DATA_MATRIX, SCOLUMN)
    End If
Loop Until EXCHANGE_FLAG = False And SROW = LBound(DATA_MATRIX, SCOLUMN)

BINARY_SORT_FUNC = DATA_MATRIX

Exit Function
ERROR_LABEL:
BINARY_SORT_FUNC = Err.Number
End Function

 

[Juggler_IN]

Say for the Array(5,3,4,6,1)

 

[Rick Rothstein]

Your function requires a two-dimensional array... the Array function only produces a one-dimensional array. You can use this to produce a two-dimensional array equivalent to a single column of values...

[{5;3;4;6;1}]

The square brackets is the shortcut syntax for the Evaluate function... the curly braces are Excel's symbol for an array constant. If you wanted to create a two-dimensional array equivalent to an area of cells (for this example, 3 rows by 5 columns), try this...

[{5,8,2,4,7;6,1,3,9,4;2,4,6,8,3}]

where the commas create values along a row and the semi-colon moves down one row.

 

[Juggler_IN]

@Rick Rothstein; the function works on a single column of data in a sheet through in coding it a 2d coding.

 

[Rick Rothstein]

@Rick Rothstein; the function works on a single column of data in a sheet through in coding it a 2d coding.

When you assign a range to a Variant variable, even if that range is a single column of values, VBA assigns that range to the variable as a two-dimensional array. As I said in my earlier reply, the Array function only produces a one-dimensional array so your code cannot use it (it will choke when you use 2 as the second argument to the LBound and UBound function calls). So, you need to use what I posted in my earlier message to produce the required two-dimensional array, namely, this...

[{5;3;4;6;1}]

I am curious, though... if your function only works on a single column of data, why are you performing this calculation in your code...

SCOLUMN = LBound(DATA_MATRIX, 2)
NCOLUMNS = UBound(DATA_MATRIX, 2)

Don't you already know both of those values will be equal to 1 for ranges and properly created arrays?

 

[Juggler_IN]

Sub Test()

Dim v

v = [{5;3;4;6;1}]

Debug.Print Evaluate(BINARY_SORT_FUNC(v))

End Sub

Outputs only 1.

 

[Marcelo Branco]

This worked for me

Sub Test()
Dim v, i As Long, arr As Variant

v = [{5;3;4;6;1}]
arr = BINARY_SORT_FUNC(v)

For i = 1 To UBound(v, 1)
   Debug.Print arr(i, 1)
Next i
End Sub

M.