UDF Error: Compile Error Array Expected - I do need help with this function

[greegan]

The following is Part 1 of a custom function problem I'm having. Thank you for your assistance.

Function FindFedIntPg(I As Long, Period As String, Cntry As String)
Dim GetFedTableBase As Variant
Dim Tween As Boolean
Dim GetTableIncr As Double
Dim Cat1, Cat2, Cat3, Cat4, Cat5, Cat6, Cat7, Cat8 As Boolean
Dim Pg As Double
    If Pg = 1 Then
        GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
        Array(2, 4, 4, 8))
    ElseIf Pg = 2 Then
        GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
        Array(4, 8, 8, 18))
    ElseIf Pg = 3 Then
        GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
        Array(8, 16, 18, 34))
    ElseIf Pg = 4 Then
        GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
        Array(12, 24, 26, 52))
    ElseIf Pg = 5 Then
        GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
        Array(16, 32, 34, 70))
    ElseIf Pg = 6 Then
        GetTableIncr = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
        Array(20, 40, 44, 86))
 End If
 

    If Cntry = "CA" Then
        GetFedTableBase = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
        Array(248, 495, 537, 1072))
    ElseIf Cntry = "OC" Then
        GetFedTableBase = WorksheetFunction.Lookup(Period, Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), _
        Array(248, 495, 537, 1072))
    End If
Cat1 = GetFedTableBase(Cntry, Period) - GetFedTableBase(Cntry, Period)
Cat2 = GetFedTableBase(Cntry, Period)
Cat3 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period)
Cat3 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period) + 55 * (GetTableIncr(2, Period))
Cat4 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period) + 55 * (GetTableIncr(2, Period)) + 55 * (GetTableIncr(3, Period))
Cat5 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period) + 55 * (GetTableIncr(2, Period)) + 55 * (GetTableIncr(3, Period)) _
    + 55 * (GetTableIncr(4, Period))
Cat6 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period) + 55 * (GetTableIncr(2, Period)) + 55 * (GetTableIncr(3, Period)) _
    + 55 * (GetTableIncr(4, Period)) + 55 * (GetTableIncr(5, Period))
Cat7 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period) + 55 * (GetTableIncr(2, Period)) + 55 * (GetTableIncr(3, Period)) _
    + 55 * (GetTableIncr(4, Period)) + 55 * (GetTableIncr(5, Period)) + 55 * (GetTableIncr(6, Period)) - 0.01
If Target >= Low And Target <= High Then
Tween = True
Else
Tween = False
End If

If Tween(I, Cat1, Cat2) = True Then FindFedIntPg = 1
ElseIf Tween(I, Cat2, Cat3) = True Then FindFedIntPg = 2
ElseIf Tween(I, Cat3, Cat4) = True Then FindFedIntPg = 3
ElseIf Tween(I, Cat4, Cat5) = True Then FindFedIntPg = 4
ElseIf Tween(I, Cat5, Cat6) = True Then FindFedIntPg = 5
ElseIf Tween(I, Cat6, Cat7) = True Then FindFedIntPg = 6
End If

End Function

I'm getting a compile error: Expected array and GetTableIncr is highlighted in the Cat1= formula.
In my cell I have it structured as I=3000,Period=Biweekly,Cntry=CA

Basically, if I falls between two values, it should produce a number ranging from 1 to 6.

Cheers,

-- G

 

[wigi]

Hello

I don't follow you, sorry.

I'm getting a compile error: Expected array and GetTableIncr is highlighted in the Cat1= formula.

What line of code is this?

If I understand the aim of this correctly, there are quite a number of inconsistencies and strange codes in that function.
You might want to review it line by line (using F8) because I'm afraid you're not close to a solution. Unless I did not understand the aim of course.

 

[greegan]

Cat3 = GetFedTableBase(Cntry, Period) + 54 * GetTableIncr(1, Period)

I'm very sorry, it was Cat3 where the Expected array is highlighting GetTableIncr

-- G

 

[wigi]

GetFedTableBase
is a Variant, but it always seems to be a number (248, 495, 537, 1072). What is the purpose?

Why do you have this split up for CA and OC - the definition is the same ???

 

[wigi]

Things like this won't work as your array is not sorted:

WorksheetFunction.Lookup("Weekly", Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly"), Array(20, 40, 44, 86))

I would advise to store that array only once, and to sort its contents:

Sub test()

    arr = Array("Weekly", "Biweekly", "Semi-Monthly", "Monthly")
    MsgBox WorksheetFunction.Lookup("Weekly", arr, Array(40, 86, 44, 20))

End Sub

 

[greegan]

Thank you for responding Wigi.
CA and OC represent Canada and Out of Canada. These numbers can change at any time so I've separated them. These also represent separate tax table files.
These numbers are the starting numbers on the tax tables. Each table has 6 pages. Each row there is a range of numbers where the Income will fall within. So where Weekly begins at 248, the first range is 0-248, the next is 248-250 (minus 0.01), 250-252 (minus 0.01) and so on.
Each page, depending on the Period (Weekly, Biweekly, Semi-Monthly and Monthly), decides how large a range it has on each row of the table page, increasing this per page.In the case where the table we want to look at is Weekly, for pages 1 thru 6 the increments would be 2,3,8,12,16,20. For the Weekly table of the Federal taxes, the range would be 248-3656.

I want to find the range (row with greater than and less than values) where the identified income falls within. I will then find the median value within that range and that is the final number I need. I've done most of this in cells. I'm trying to skip the range reference and go straight to one formula.

Thank you again for any and all assistance you can provide.

-- g

 

[greegan]

Thank you for responding Wigi.
CA and OC represent Canada and Out of Canada. These numbers can change at any time so I've separated them. These also represent separate tax table files.
These numbers are the starting numbers on the tax tables. Each table has 6 pages. Each row there is a range of numbers where the Income will fall within. So where Weekly begins at 248, the first range is 0-248, the next is 248-250 (minus 0.01), 250-252 (minus 0.01) and so on.
Each page, depending on the Period (Weekly, Biweekly, Semi-Monthly and Monthly), decides how large a range it has on each row of the table page, increasing this per page.In the case where the table we want to look at is Weekly, for pages 1 thru 6 the increments would be 2,3,8,12,16,20. For the Weekly table of the Federal taxes, the range would be 248-3656.

I want to find the range (row with greater than and less than values) where the identified income falls within. I will then find the median value within that range and that is the final number I need. I've done most of this in cells. I'm trying to skip the range reference and go straight to one formula.

Thank you again for any and all assistance you can provide.

-- g

 

[greegan]

Thank you (regarding the array) I didn't notice that. I will update that immediately.

-- g

 

[wigi]

You declare GetTableIncr as a Double, yet I see in the code things like:

GetTableIncr(1, Period)

What do you want to say with that?

 

[greegan]

You declare GetTableIncr as a Double, yet I see in the code things like:

GetTableIncr(1, Period)

What do you want to say with that?

1 is the page number, we are trying to determine the increments the page represents based on the table (period)