Run Time Error 5 Invalid Procedure or Error Argument

[Ed Song]

The code where the error code occurs is as follows:

'Calculate Production function
Output_t1 = GrowthFactor ^ (Period_Length) * (Output_t0 + FactorProductivity * (1 - Damages_Production_t1) * _
(1 - AbatementCost_Production) * (CapitalStock_t1 - CapitalStock_t0) ^ Share_K_Capital * _
(Labor_t0 ^ Share_Labor))


What's funny is that when I first ran the program it worked several times without error. Then I changed the value of dep_K on the excel sheet and got the message. The program no longer worked even when I changed the value of dep_K back to the original value (actually it worked off and on for a couple of times before it never worked again). I had a backup copy and tried that one, too. Again the exact same results. It worked until I changed the value of dep_K and didn't work even when I changed it back to the original value.

I also played around to see which terms caused the error. Apparently the problem is with (CapitalStock_t1 - CapitalStock_t0). The program runs every time I take that term out. The program runs with all of the remaining variable. I've rewritten the term, but no luck. I've checked to see if I missed declare the terms, but again no luck.

Anyone have any suggestions? What's wrong with this code? This code is embedded in the following double Do Until loops. Maybe there is an error in the loops?

'Begin Outer Loop XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX End Outer Loop
'The outerloop is the time loop. After variables for a time period is calculated, the program exits the inner loop into the outerloop to update the
'time period.
    
 'Set Counter
 Dim t As Long
 t = 1
 
Do Until t = 102


'First the economy flatlines (dies) if the survivability function is zero.


If Survivability_Function_t0 = 0 Then
    Output_t1 = 0
    CapitalStock_t1 = 0
    Consumption_t1 = 0
    Investment_t1 = 0
    Government_t1 = 0
    TotalDeadWtLoss_t1 = 0
    Energy_Usage_Consumption_t1 = 0
    Energy_Usage_Production_t1 = 0
    Energy_Usage_t1 = 0
    AtmoEmissions_t1 = 0
    CO2_Level_t1 = AtmoEmissions_t1 + (CO2_Equilibrium_Fraction_Atm * (CO2_Level_t0 - CO2_Equilibrium)) + _
                    ((1 - CO2_Equilibrium_Fraction_Atm) * (1 - CO2_TransferRate_to_Atm) * (CO2_Level_t0 - CO2_Equilibrium)) + CO2_Equilibrium
    CO2_Transient_t1 = (1 - CO2_Equilibrium_Fraction_Atm) * AtmoEmissions_t1 + (1 - CO2_Equilibrium_Fraction_Atm) * _
                       (1 - CO2_TransferRate_to_Atm) * (CO2_Level_t0 - CO2_Equilibrium)
    New_CO2_Equilibrium_t1 = CO2_Level_t1 - CO2_Transient_t1
    Damages_Utility_t1 = 1
    Damages_CapitalStock_t1 = 1
    Damages_Production_t1 = 1
    Damages_Y_Composite_t1 = 1
    Damages_Composite_t1 = 1
    
    'If the economy is still living, then proceed through innerloop. Otherwise, go to outerloop.
    Else
            
'Begin Inner Loop xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Begin Inner Loop
'In the inner loop, an iteration process is used to solve for the damages, and production simultaneity problem. First solve the problem using the
'last periods damages, then calculate a new implied damage quantity. Then use the new computed damages to solve for production. Repeat the process until
'a solution converges.


    
    'Declare and set counter
    Dim w As Long
    
    w = 1
    
    'Declare and set conditional statement variable
    Dim Abs_Error As Double

Abs_Error = 0.1




    Do Until Abs_Error < 0.005
    
        'Assume Damages are equal to their last computed values
        Damages_CapitalStock_t1 = Damages_CapitalStock_t0
        Damages_Utility_t1 = Damages_Utility_t0
        Damages_Production_t1 = Damages_Production_t0
        Damages_Composite_t1 = Damages_Composite_t0




        'Calculate Capital Stock. Remember, damages to capital stock acts like another depreciation rate.
        CapitalStock_t1 = Investment_t0 + (1 - Dep_k) ^ Period_Length * (1 - Damages_CapitalStock_t0) ^ Period_Length * CapitalStock_t0


        'Calculate Production function
        Output_t1 = GrowthFactor ^ (Period_Length) * (Output_t0 + FactorProductivity * (1 - Damages_Production_t1) * _
        (1 - AbatementCost_Production) * (CapitalStock_t1 - CapitalStock_t0) ^ Share_K_Capital * _
                    (Labor_t0 ^ Share_Labor))
        
        
        'Calculate World Accounting Equation: Consumption, Investment and Government Spending.


        Consumption_t1 = (Share_Consumption * Output_t1) * (1 - AbatementCost_Consumption) ^ (1 / (1 + MU_Elasticity))
        Investment_t1 = Share_Investment * Output_t1 + (1 - (1 - AbatementCost_Consumption) ^ (1 / (1 + MU_Elasticity)))
        Government_t1 = Share_Government * Output_t1
        TotalDeadWtLoss_t1 = Output_t1 - Consumption_t1 - Investment_t1 - Government_t1
    
    'Note: AtmoEmissions is the same as retained emissions.
    
        'Energy Balance
        Energy_Usage_Consumption_t1 = Energy_to_Consumption_Ratio * Consumption_t1
        Energy_Usage_Production_t1 = Energy_to_Production_Ratio * Output_t1
        Energy_Usage_t1 = Energy_Usage_Consumption_t1 + Energy_Usage_Production_t1
        AtmoEmissions_Consumption_t1 = Emissions_Consumption_Ratio * Consumption_t1
        AtmoEmissions_Output_t1 = Emissions_Output_Ratio * Output_t1
        AtmoEmissions_t1 = AtmoEmissions_Consumption_t1 + AtmoEmissions_Output_t1
        
        'CO2 Level
        CO2_Level_t1 = AtmoEmissions_t1 + (CO2_Equilibrium_Fraction_Atm * (CO2_Level_t0 - CO2_Equilibrium)) + _
                    ((1 - CO2_Equilibrium_Fraction_Atm) * (1 - CO2_TransferRate_to_Atm) * (CO2_Level_t0 - CO2_Equilibrium)) + CO2_Equilibrium
        CO2_Transient_t1 = (1 - CO2_Equilibrium_Fraction_Atm) * AtmoEmissions_t1 + (1 - CO2_Equilibrium_Fraction_Atm) * _
                       (1 - CO2_TransferRate_to_Atm) * (CO2_Level_t0 - CO2_Equilibrium)
        New_CO2_Equilibrium_t1 = CO2_Level_t1 - CO2_Transient_t1
        
        'New Temperature Deviation
        Dim Temp_Off_t1 As Double
        Temperature_Critical = Sheets(1).Range("K28")
    
        Forcing_t1 = 3.35 * (Log(1 + 1.2 * CO2_Level_t1 + 0.005 * CO2_Level_t1 ^ 2 + 1.4 * 10 ^ (-6) * CO2_Level_t1 ^ 3) _
                    - Log(1 + 1.2 * CO2_Equilibrium + 0.005 * CO2_Equilibrium ^ 2 + 1.4 * 10 ^ (-6) * CO2_Equilibrium ^ 3))
    
        Temp_Off_t1 = ClimateSensitivity_Lambda * Forcing_t1
        If Temp_Off_t1 < Temperature_Critical Then
                Switch = 0
                Else
                    Switch = 1
                    End If
                      
        Temp_t1 = Temp_Off_t1 + ClimateSensitivityParameter_b2 * Switch * (CO2_Level_t1 / CO2_Equilibrium)
    
        'Damages
        Damages_Utility_t1 = 1 - (1 / (1 + Proportional_Constant_Temperature_U * Temp_t1 + Exponent_Constant_Temperature_U * Temp_t1 ^ 2))
        Damages_Production_t1 = 1 - (1 / (1 + Proportional_Constant_Temperature_Y * Temp_t1 + Exponent_Constant_Temperature_Y * Temp_t1 ^ 2))
        Damages_CapitalStock_t1 = 1 - (1 / (1 + Proportional_Constant_Temperature_K * Temp_t1 + Exponent_Constant_Temperature_K * Temp_t1 ^ 2))
        Damages_Y_Composite_t1 = 1 - (1 - Damages_Production_t1) * (1 - Damages_CapitalStock_t1) ^ Share_K_Capital
        Damages_Composite_t1 = Damages_Utility_t1 + (1 - Damages_Utility_t1) * Damages_Utility_t1 * (Damages_Y_Composite_t1 ^ MU_Elasticity)
        
                   
        'Begin Error Calculations
        'Termination or continuance condition of iteration process. If error is small enough terminate loop. Termination also occurs
     'when 100 iteration attempts have been made.
     
        
        If w = 1 Then
            Abs_Error = 0.001
            End If
        
        If w > 1 And w < 100 Then
            Abs_Error = Abs((Output_t1 - Output_t0) / Output_t0)
                End If
                
    If Abs_Error > 0.005 Then
    
    'Reset t1 variables as t0 variables and start at the beginning of the loop.
        Damages_CapitalStock_t0 = Damages_CapitalStock_t1
        Damages_Utility_t0 = Damages_Utility_t1
        Damages_Production_t0 = Damages_Production_t1
        Damages_Y_Composite_t0 = Damages_Y_Composite_t0
        Damages_Composite_t0 = Damages_Composite_t1
        
     End If
        
        'Termination or continuance condition of iteration process. If error is small enough terminate loop. Termination also occurs
        'when 100 iteration attempts have been made.
     
     w = w + 1
    
      
    Loop
        'End Inner Loop xxxxxxxxxxxxxxxxxxxxx'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx End Inner Loop
    End If
        
        
   'Utility and welfare calculations
   
        'Survivability Probability
        Survivability_Function_t1 = 1 - (Temp_t1 ^ 2 / (Temp_Max ^ 2 + 1.96 * (Temp_Max - Temp_t1) ^ 2))
        
        If Survivability_Function_t1 <= 0 Then
            Temp_t1 = SKI_Limit_Temp
            Survivability_Function_t1 = 0
            End If
 
        'Utility Index
        Raw_Utility_Index_t1 = (Survivability_Function_t1 * (Consumption_t1 ^ MU_Elasticity)) / MU_Elasticity
        Utility_Index_t1 = Utility_Normalization_Factor * Raw_Utility_Index_t1
    
        'Welfare
        Welfare_t1 = Welfare_t0 + Discount_Factor ^ Period_Length * Utility_Index_t1
   
    
    'Print output
    Sheets(1).Range("P" & 3 + t, "AK" & 3 + t).Value = _
    Array(Output_t1, CapitalStock_t1, Consumption_t1, Investment_t1, Government_t1, TotalDeadWtLoss_t1, _
        Energy_Usage_Consumption_t1, Energy_Usage_Production_t1, Energy_Usage_t1, AtmoEmissions_t1, CO2_Level_t1, _
        CO2_Transient_t1, New_CO2_Equilibrium_t1, Temp_t1, Damages_Utility_t1, Damages_CapitalStock_t1, Damages_Production_t1, _
        Damages_Y_Composite_t1, Damages_Composite_t1, Survivability_Function_t1, Utility_Index_t1, Welfare_t1)
        
        
        
     'Reset stock variables and survivability function
     
     Investment_t0 = Investment_t1
     CapitalStock_t0 = CapitalStock_t1
     Output_t0 = Output_t1
     CO2_Level_t0 = CO2_Level_t1
     Welfare_t0 = Welfare_t1
     Survivability_Function_t0 = Survivability_Function_t1
        
    t = t + 1
        
Loop

 

[oldbrewer]

I would build the function up from scratch starting with ONLY the term that you KNOW causes the error. If that runs ok add another term, and so on.........

 

[Ed Song]

I would build the function up from scratch starting with ONLY the term that you KNOW causes the error. If that runs ok add another term, and so on.........

Oldbrewer, thanks for your response. The program did not run with only the offending code. In fact, it did not run with just CapitalStock_t1 nor just CapitalStock_t0. When I did what you suggest, I get a new Error 5 message, but this time the offending code is:

Raw_Utility_Index_t1 = (Survivability_Function_t1 * (Consumption_t1 ^ MU_Elasticity)) / MU_Elasticity

However, if I only remove CapitalStock_t1 - CapitalStock_t0, the program runs as it should.

Unfortunately, if I use the equation Output_t1 = 1.03*Output_t0, then I get an error 5 message with the offending code as Raw_Utility_Index_t1 = .....

 

[oldbrewer]

Output_t1 = GrowthFactor ^ (Period_Length) * (Output_t0 + FactorProductivity * (1 - Damages_Production_t1) * _
(1 - AbatementCost_Production) * (CapitalStock_t1 - CapitalStock_t0) ^ Share_K_Capital * _
(Labor_t0 ^ Share_Labor))

Output_t1 = GrowthFactor ^ (Period_Length) make this the only line in a new macro and put in cells(1,104)=Output_t1
check cell CZ1 to ensure the value is what you expect now add next bit

aahhhhh

Output_t1 = GrowthFactor ^ (Period_Length) * (Output_t0 + FactorProductivity * (1 - Damages_Production_t1) * _
(1 - AbatementCost_Production) * (CapitalStock_t1 - CapitalStock_t0) ^ Share_K_Capital * _
(Labor_t0 ^ Share_Labor))

* (Output_t0 + FactorProductivity * (1 - Damages_Production_t1)

you are missing some brackets

* _ is this multiply by minus something ?

 

[Ed Song]

Following your suggestion, the macro worked, and the right values were put in cells CZ1.

The equation should be in the form of y1 = g^l * (y0 + A*(1-dp)*(1-c)*(k1-k2)^b * (l^e))

let g^1 = g
and l^ = l
z = y1
y0 = y
dp = d

Then the equation should be

z = g * (y + A*(1-d)*(1-c)*(k1-k2)^b * l)

or

z = g*y + g*A*l*(1-d)*(1-c)*(k1-k2)^b

As far as I can see, my equation should work. What do you see that I don't see?

As for * _

That is correct in the program, its a multiplication symbol followed by a space and then an underscore to indicate that the equation continues to the next line. So that's not the problem.

 

[Ed Song]

Is my file corrupted? Can anyone answer this question? I have a weird solution that seems to work. It goes like this:


Dim Cap1 As Double
Dim Cap2 As Double


Cap0 = CapitalStock_t0
Cap1 = CapitalStock_t1


'Calculate Production function
Output_t1 = GrowthFactor ^ (Period_Length) * (Output_t0 + FactorProductivity * (1 - Damages_Production_t1) * _
(1 - AbatementCost_Production) * (Cap1 - Cap0) ^ Share_K_Capital * _
(Labor_t0 ^ Share_Labor))

CapitalStock_t1 = Cap1
CapitalStock_t0 = Cap0

Apparently, if you rename the variable the program will work. But I know its not a Dim problem, since CapitalStock_t0 and CapitalStock_t1 variables do work.

 

[Ed Song]

Because nobody can help me, and I've now spent hundreds of dollars talking to experts including experts on this forum, I've decided to abandon my project.

I feel so sad nobody can help me. What is Run Time Error 5? How come nobody can help? I suspect its a virus.

 

[Michael M]

In post#5 you have stated, "the macro worked"
The microsoft help also suggests that the error 5 might be caused by your calculation exceeding the parameters and values allowed.

https://msdn.microsoft.com/en-us/library/aa445484(v=vs.60).aspx

As oldbrewer suggested earlier, I'd go back to basics and replace the named ranges with actual variables and build the formula one step at a time, until it either lives or dies !!
I'd also consider breaking the calculation down into smaller nodes, so that rather 1 long calc, you have 2,3,4,5 or more smaller ones that actually achieve the desired result, then you can combine them back as one.

 

[Ed Song]

Michael M, thanks for your response.

For 11 days and nights I and a number of paid experts have looked at my program term by term, line by line. I have tested every single parameter and variable using Debug.Print Variable. I started the program from scratch and dissected each statement variable by variable, doing every suggestion I have heard. The error cannot be found. And yes, I have broken down the equations to a system of shorter equations.

The biggest number in the program is not that large, probably less than a billion so I don't know why the program couldn't handle the numbers, especially since all the variables have been declared as Doubles (at least all the variables that must be determined by calculation). I thought the problem could be with the iteration, so I simplified the program, using past variables as instruments such that I would have straight forward linear equations to solve. The program always fails at the same line. At first, it was the difference in the capital stocks causing the problem. I then purchased a new version of excel, and now the problem is always with the labor or/and labor share term.

The simplified version is straight forward and easy. Excel should have no problem handling it. If it works on an Excel spreadsheet, I'll see if I want to get it working as a VBA program.

 

[Michael M]

This isn't a worksheet event is it ??