Reference complete dynamic array into function within VBA

[spencer_time]

Hello, I am trying to modify a partial solution to a question I had 2 days ago, and I came into a new question.

I am trying to use Application.WorksheetFunction.Max and have it reference all of the values within a dynamic array. What I have tried (both with and without parentheses) thus far is below:

MaxVal = Application.WorksheetFunction.Max (LBound(ary) To UBound(ary))

It is not functioning, giving me the error "Compile error: Expected: List separator or )"

Although I am not certain, I believe this to be a syntax issue...when referencing internet searches I see that the correct format is Application.WorksheetFunction.Max(arg1, arg2, arg3……………arg30) and I don't know how to make this work with an array.

 

[Fluff]

Try

MaxVal = Application.WorksheetFunction.Max(ary)

 

[spencer_time]

Thank you Fluff for the prompt reply!

It no longer has a compile error, however when I use the function it results in #VALUE and when debugging it states that "A value used in the formula is the wrong type of data."

If it helps the workbook can be found here and the original and modified version of the code given to me yesterday is below;

ORIGINAL JGordan11 suggestion:

Option Explicit

Function AvgAbsVal(RowsToIgnore As Variant, ParamArray Ranges() As Variant) As Double
'Returns the average absolute value of the cells in Ranges excluding cells with row numbers in RowsToIgnore
'Can use Function IgnoreRows for the RowsToIgnore Parameter to have dynamic updating of the row numbers to ignore
'Or can hard code an array for RowsToIgnore like {2,4,6}, use {0} to ignore no rows.  This will not dynamically update
'Function also ignores non-numeric values and empty cells in Ranges
'Also outputs function information to the immediate window
    Dim R, C, RowOmit, tot As Double, KeepCount As Long, IgnoreCount As Long, t As Double, KeepC As Boolean
    t = Timer
    For Each R In Ranges
        For Each C In R
            If IsNumeric(C) And C <> "" Then
                KeepC = True
                For Each RowOmit In RowsToIgnore
                    If C.Row = RowOmit Then
                        KeepC = False
                        IgnoreCount = IgnoreCount + 1
                        Exit For
                    End If
                Next
                If KeepC Then
                    KeepCount = KeepCount + 1
                    tot = tot + Abs(C)
                End If
            Else
                IgnoreCount = IgnoreCount + 1
            End If
        Next
    Next
    AvgAbsVal = tot / KeepCount
    Debug.Print "Average of " & KeepCount & " values (ignored " & IgnoreCount & "). Calculation time = " & Timer - t & " seconds."
End Function

Function IgnoreRows(ParamArray OmitRows() As Variant) As Variant
    Dim OmitRow, i As Long
    ReDim a(0 To UBound(OmitRows))
    For Each OmitRow In OmitRows
        a(i) = OmitRow.Row
        i = i + 1
    Next
    IgnoreRows = a
End Function

MODIFIED to use another (MAX) function:

Option Explicit

Function MaxVal(RowsToIgnore As Variant, ParamArray Ranges() As Variant) As Double 'EDIT

    Dim R, C, RowOmit, tot As Double, KeepCount As Long, IgnoreCount As Long, t As Double, KeepC As Boolean
    Dim ary() As Double 'EDIT
        t = Timer
    For Each R In Ranges
        For Each C In R
            If IsNumeric(C) And C <> "" Then
                KeepC = True
                For Each RowOmit In RowsToIgnore
                    If C.Row = RowOmit Then
                        KeepC = False
                        IgnoreCount = IgnoreCount + 1
                        Exit For
                    End If
                Next
                If KeepC Then
                    ary(KeepCount) = C 'EDIT
                    KeepCount = KeepCount + 1
                    'tot = tot + Abs(C) 'REMOVED
                End If
            Else
                IgnoreCount = IgnoreCount + 1
            End If
        Next
    Next
    'AvgAbsVal = tot / KeepCount 'REMOVED
    MaxVal = Application.WorksheetFunction.Max(ary) 'EDIT
    Debug.Print "Maximum of " & KeepCount & " values (ignored " & IgnoreCount & "). Calculation time = " & Timer - t & " seconds." 'EDIT
End Function

To JGordan11's credit, I wasn't specific enough when I asked for help (and his code did work for that specific case). I asked for a way to omit ranges programatically from a function/formula and gave the average of absolute value as an example, but I have other formula that I need to do the same thing with, so I'm trying to learn how to modify this with other functions myself instead of treating him like a paid employee and asking him to make a version for all of these formula O_O

 

[Fluff]

Try it like

Function MaxVal(RowsToIgnore As Variant, Ranges As Range) As Double 'EDIT

    Dim R, C, RowOmit, tot As Double, KeepCount As Long, IgnoreCount As Long, t As Double, KeepC As Boolean
    Dim ary As Variant
    ReDim ary(1 To Ranges.Rows.Count)
    KeepCount = 1
        t = Timer
    For Each R In Ranges
        For Each C In R
            If IsNumeric(C) And C <> "" Then
                KeepC = True
                For Each RowOmit In RowsToIgnore
                    If C.Row = RowOmit Then
                        KeepC = False
                        IgnoreCount = IgnoreCount + 1
                        Exit For
                    End If
                Next
                If KeepC Then
                    ary(KeepCount) = C 'EDIT
                    KeepCount = KeepCount + 1
                    'tot = tot + Abs(C) 'REMOVED
                End If
            Else
                IgnoreCount = IgnoreCount + 1
            End If
        Next
    Next
    'AvgAbsVal = tot / KeepCount 'REMOVED
    MaxVal = Application.WorksheetFunction.Max(ary) 'EDIT
    Debug.Print "Maximum of " & KeepCount & " values (ignored " & IgnoreCount & "). Calculation time = " & Timer - t & " seconds." 'EDIT
End Function

 

[spencer_time]

Thank you again Fluff, that did work.

In looking through that I see the array is specified as a variant (which I had originaly and tried making it double in an attempt to fix it) and defining keepcount = 1. Is there anything else that I missed? If not, I'm having trouble understanding why his original version worked as AvgAbs but had a datatype issue when I changed it to Max. Would take too long or be difficult to explain it for me?

 

[Fluff]

Your array had no size & so when you tried to store a value in it, it failed.

 

[spencer_time]

I see, you redimensioned it too, that makes since.

Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.