Solved

Subscript out of range problem

Posted on 2011-03-13
5
451 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Run code from text file in vb 1 85
Spell Check in VB6 13 135
Zip Folders Using Chilkat Routines 1 71
VBA to find and replace multiline text from VBA modules 8 89
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

740 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