• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Subscript out of range problem

Hello Experts,

I am having a small problem with  a function I have written. I need to pass an aary out of the function but currently I am getting the subscript out of range error on line 361. Could you please take a look at the code and tell me where you think I have gone wrong.          
Function Benefit_Calc(No_benefits As Long, t As Long) As Variant()

'Looping through the different elements of the calculation

Dim element(1 To 9) As Variant
Dim BasicCriteria_type(1 To 5) As Variant
Dim B_table As String
Dim Benefittable As Variant

Dim agenttype_valid As Variant
Dim time_type As String
Dim agenttype_start As String
Dim time_range As String
Dim minrange As Long
Dim maxRange As Long
Dim benefit As Long
'Dim No_benefits As Long
Dim Criteria_type As String
'Dim t As Long
Dim t_adjustment As Long
Dim agenttype_flag As Long
Dim TimeA() As Variant
Dim time_flag As Long
Dim criteria_No As Long
Dim criteria_table As String
Dim Criteria() As Variant
Dim criteria_flag As Long
Dim criteria_limittype As String
Dim criteria_limit As String
Dim element_no As Long

Dim DRIVER_SCALAR As String
Dim DRIVER_LIST As String
Dim DRIVER_DEFINE As String
Dim DRIVER_BASICVARIABLE As String
Dim DRIVER_ADDOVER As String
Dim DRIVER_ADD_TYPE As String
Dim DRIVER_APPLYFACTOR As String
Dim DRIVER_FACTORTABLE As String
Dim DRIVER_SCALARAMOUNT  As String
Dim DRIVER_TABLETYPE As String
Dim DRIVER_TABLE As String
Dim driver As Variant
Dim table_value As Variant
Dim Benefit_amount As Variant
Dim Agent_Benefits As Variant
Dim max_factor As Variant
Dim DRIVER8 As Variant
Dim Ben_Array As Variant

ReDim Agent_Benefits(1 To No_benefits + 1)
ReDim Criteria(1 To Proj_prd)

For benefit = 1 To No_benefits

' find the benefittable to use.
 
    B_table = "BOR" & benefit
     
    Benefittable = Table_touse("Benefit", B_table, "N")
    
    'read through all inputs required for this elements
    
    agenttype_valid = Read_table(Benefittable, "agenttype_valid", "Value", "String", "String")
    time_type = Read_table(Benefittable, "time_type", "Value", "String", "String")
    agenttype_start = Read_table(Benefittable, "agenttype_start", "Value", "String", "String")
    time_range = Read_table(Benefittable, "time", "Value", "String", "String")
    Criteria_type = Read_table(Benefittable, "criteria_type", "Value", "String", "String")
    minrange = Read_table(Benefittable, "time_start", "Value", "String", "String")
    maxRange = Read_table(Benefittable, "time_end", "Value", "String", "String")
    BasicCriteria_type(1) = Read_table(Benefittable, "Basic_Criteria1", "Value", "String", "String")
    BasicCriteria_type(2) = Read_table(Benefittable, "Basic_Criteria2", "Value", "String", "String")
    BasicCriteria_type(3) = Read_table(Benefittable, "Basic_Criteria3", "Value", "String", "String")
    BasicCriteria_type(4) = Read_table(Benefittable, "Basic_Criteria4", "Value", "String", "String")
    BasicCriteria_type(5) = Read_table(Benefittable, "Basic_Criteria5", "Value", "String", "String")

    
    '*****************************************
    '**************Criteria*******************
    '*****************************************
    
    '*****************************************
    '***********Agent_type flag***************
    'Checks that the the current agent type is valid for the benefit.
    
    If InStr(1, agenttype_valid, Agent_type(t)) = 1 Or agenttype_valid = "all" Then '<------------- can I use Instr formula here instead
    
        If agenttype_start <> "n/a" Then
            
            If time_type = "time_intype" Then
    
                t_adjustment = Agent_timeintype(t)
    
            ElseIf time_type = "time_joining" Then
    
                t_adjustment = Agent_duration(t)
                
            End If
            
            If InStr(1, agenttype_start, Agent_type(t - t_adjustment + 1)) Then
            
            agenttype_flag = 1
            
            Else
            
            agenttype_flag = 0
            
            End If
    
       Else
    
           agenttype_flag = 1
    
       End If
    
    Else
            
        agenttype_flag = 0
        
    End If
    
    
    '*******Exit Function/Loop****************
    If agenttype_flag = 0 Then
        
    GoTo nxtbenefit
    
    End If
    
    
    '*****************************************
    '************Time_flag********************
    'check that the time is ok
    
    If time_type = "time_intype" Then
    
        TimeA() = Agent_timeintype()
    
    ElseIf time_type = "time_joining" Then
    
        TimeA() = Agent_duration()
        
    End If
    
    
    'Time ranges
      
    If time_range = "all" Then
    
        time_flag = 1
    
    ElseIf time_range = "range" Then
     
        If TimeA(t) <= minrange And TimeA(t) >= maxRange Then
       
            time_flag = 1
          
        Else
            time_flag = 0
        
        End If
    
    End If
    

    '*******Exit Function/Loop****************
    If time_flag = 0 Then
        
    GoTo nxtbenefit
    
    End If
    
    
    '**********************************************
    '************Basic_criteria********************
    '**********************************************
    
    ' need to loop through all 5 criteria
    
    'Criteria
    'Checks that the basic criteria are met.
    
    
    For criteria_No = 1 To 5
    
     
           criteria_table = "BORC" & benefit
          
     
        If BasicCriteria_type(criteria_No) = "Persistency" Then
        
            Criteria(t) = Agent_persistency(t)
        
        ElseIf BasicCriteria_type(criteria_No) = "Persistency " Then
        
            Criteria(t) = Agent_persistency(t)
            
        ElseIf BasicCriteria_type(criteria_No) = "Persistency" Then
        
            Criteria(t) = Agent_persistency(t)
            
        ElseIf BasicCriteria_type(criteria_No) = "Persistency" Then
        
            Criteria(t) = Agent_persistency(t)
            
        ElseIf BasicCriteria_type(criteria_No) = "Persistency" Then
        
            Criteria(t) = Agent_persistency(t)
        
        ElseIf BasicCriteria_type(criteria_No) = "n/a" Then
        
            criteria_flag = 1
           
           GoTo nxtcriteria
            
        End If
        
        
        'get whether criteria is more than or less than
        
        ' need to read criteria table for this benefit.
        
        criteria_limittype = Table_read("Benefit", criteria_table, "N", BasicCriteria_type(criteria_No), "criteria_limittype", "String", "String")
        criteria_limit = Table_read("Benefit", criteria_table, "N", BasicCriteria_type(criteria_No), Agent_type(t), "String", "String")
        
        
        If criteria_limittype = "more_than" Then
        
            If Criteria(t) >= criteria_limit Then
            
                criteria_flag = 1
                
            Else
            
                criteria_flag = 0
                    
            End If
        
        ElseIf criteria_limittype = "less_than" Then
        
            If Criteria(t) <= criteria_limit Then
            
                criteria_flag = 1
                
            Else
            
                criteria_flag = 0
                    
            End If
            
        End If
            
            
        If criteria_flag = 0 Then
        
          GoTo nxtbenefit
         
        End If
    
nxtcriteria:
    
    Next criteria_No
        
    For element_no = 1 To 7
    
        
        DRIVER_SCALAR = Read_table(Benefittable, "DRIVER" & element_no & "_SCALAR", "Value", "String", "String")
        DRIVER_LIST = Read_table(Benefittable, "DRIVER" & element_no & "_LIST", "Value", "String", "String")
        DRIVER_DEFINE = Read_table(Benefittable, "DRIVER" & element_no & "_DEFINE", "Value", "String", "String")
        DRIVER_BASICVARIABLE = Read_table(Benefittable, "DRIVER" & element_no & "_BASICVARIABLE", "Value", "String", "String")
        DRIVER_ADDOVER = Read_table(Benefittable, "DRIVER" & element_no & "_ADDOVER", "Value", "String", "String")
        DRIVER_ADD_TYPE = Read_table(Benefittable, "DRIVER" & element_no & "_ADD_TYPE", "Value", "String", "String")
        DRIVER_APPLYFACTOR = Read_table(Benefittable, "DRIVER" & element_no & "_APPLYFACTOR", "Value", "String", "String")
        DRIVER_FACTORTABLE = Read_table(Benefittable, "DRIVER" & element_no & "_FACTORTABLE", "Value", "String", "String")
        DRIVER_SCALARAMOUNT = Read_table(Benefittable, "DRIVER" & element_no & "_SCALAR#", "Value", "String", "String")
        DRIVER_TABLETYPE = Read_table(Benefittable, "DRIVER" & element_no & "_TABLETYPE", "Value", "String", "String")
        DRIVER_TABLE = Read_table(Benefittable, "DRIVER" & element_no & "_TABLE", "Value", "String", "String")
    
    
    '**************Drivers*******************
     If DRIVER_SCALAR = "Y" Then
     
     element(element_no) = DRIVER_SCALARAMOUNT
     
     Else
     
        If DRIVER_LIST <> "N/A" Then
        
            driver = USE_ARRAY(DRIVER_LIST, t)
            
        ElseIf DRIVER_LIST = "N/A" Then
        
            If DRIVER_DEFINE = "Y" Then
           
             driver = DRIVERDEFINE(DRIVER_BASICVARIABLE, DRIVER_ADDOVER, DRIVER_ADD_TYPE, DRIVER_FACTORTABLE, t)
    
            Else
                 
                 'Error
        
            End If
        
        End If
        
        
    '************Lookup_table value**************
    
    ' reading table
        
        table_value = Table_read("Benefit", DRIVER_TABLE, "N", driver, Agent_type(t), "Range", "String")
            
        
        If DRIVER_TABLETYPE = "RATE" Then
                  
            element(element_no) = table_value * driver
                
        ElseIf DRIVER_TABLETYPE = "FIXED" Then
           
            element(element_no) = table_value
        
        End If
    
    End If
    
    Next element_no

    'max or min
    
    DRIVER8 = Read_table(Benefittable, "DRIVER8", "Value", "String", "String")
    
    If DRIVER8 = "MIN" Then
    
    max_factor = -1
    
    Else
    
    max_factor = 1
    
    End If

    element(8) = max_factor
    

   Benefit_amount = Application.WorksheetFunction.Max(element(8) * (element(1) * element(2) * element(3) - element(4) * element(5)), element(8) * element(6) * element(7))

    
    Agent_Benefits(benefit) = Benefit_amount

nxtbenefit:

Next benefit


For benefit = 1 To No_benefits

Debug.Print Agent_Benefits(benefit)

Next benefit


Benefit_Calc = Agent_Benefits()
 
 
' Benefit_Calc = Ben_Array




End Function

Open in new window

0
kuda27
Asked:
kuda27
1 Solution
 
Deepak LakkadCommented:
On Line No 361, there is following code

Benefit_Calc = Agent_Benefits()

Open in new window


In your code, I found that "Agent_Benefits" is declared as Variant
Line No 46, code is written as

Dim Agent_Benefits As Variant

Open in new window


If Agent_Benefits is a variable, how can you call it as function?

- deepak lakkad
0
 
GrahamSkanRetiredCommented:
deepak lakkad is right.

Just omit the trailing brackets:

Benefit_Calc = Agent_Benefits
0
 
EDDYKTCommented:
also redefine your function to

Function Benefit_Calc(No_benefits As Long, t As Long) As Variant
0
 
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now