Solved

Subscript out of range problem

Posted on 2011-03-13
5
447 Views
Last Modified: 2012-05-11
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
Comment
Question by:kuda27
5 Comments
 
LVL 11

Accepted Solution

by:
Deepak Lakkad earned 500 total points
ID: 35126678
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35126791
deepak lakkad is right.

Just omit the trailing brackets:

Benefit_Calc = Agent_Benefits
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 35133911
also redefine your function to

Function Benefit_Calc(No_benefits As Long, t As Long) As Variant
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35452413
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now