Run Time Error 5 Invalid Procedure or Error Argument

Ed Song

Board Regular
Joined
Sep 1, 2014
Messages
90
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?

Code:
'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
 
Last edited by a moderator:
you will soon crack it - when you run the macro how many times does it loop - and is it possible there is a counter somewhere that is NOT being set back to its initial value ?
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
My Excel program does work. I did find a potential error in the VBA program. When the temperature reaches its critical level, S(t)/S(t+1) becomes undefined for one period as S(t+1) = 0, but S(t) is still positive. However, a note of caution: this term is a recent addition to the program and the program was crashing before it hit the critical temperature. I also discovered that one of my If, then statements missed a condition which caused a cell in my Excel program go false. However, this complicated If, then statements were not in my VBA program. I'm going to check out what happens when I use (S(t) + .00001)/(S(t+1) + .00001) instead.

I haven't been thinking about a dividing by zero problem because usually when that happens VBA gives me a divided by zero error message. So if this is the problem, I don't know why I don't get a divided by zero error message.
 
Last edited:
Upvote 0
In my new version, I brought back both the time loop (100 loops) and the iteration loop (maximum 100 loops). When I look at the intermediate window, the iteration goes through the loop 2-3 times.
 
Upvote 0
you will soon crack it - when you run the macro how many times does it loop - and is it possible there is a counter somewhere that is NOT being set back to its initial value ?

Looks like the problem has finally been solved! Exactly 14 days and hundreds of dollars spent, the problem was getting confused between Government_Share and Government_Share_1. Government_Share_1 wasn't suppose to exist. But sometimes, for some reason, the program believes that = is part of the variable name and gives an error message that the variable hasn't been declared. Thus, when I wrote

Government_Share = some expression

VBA gave me an error message that Government_Share = was not declared.

Since the Excel program did not use variable names, it did not have this problem. So the excel program worked beautifully.

This caused me to change the program to:

Dim Government_Share_1
If Consumption_Share_1 >= 1 - Government_Share_1 Then
Consumption_Share = 1 - Government_Share_1
End If

Because in a subprogram the variable Government_Share was already given the cell "H53" value and called up to the main program, I thought Government_Share_1 had been given a value, but it hadn't. The mistake was hard to catch, because the printout used Government_Share. Thus, I thought that Government_Share_1 really had a positive value.

Because of this mistake, the program crashed when investment shares became negative. This occurred when carbon dioxide levels in the atmosphere became extremely large.
 
Last edited:
Upvote 0
Glad you got it solved....I hope in some small way WE have assisted you.....:beerchug:
 
Upvote 0
Unfortunately,I only had a few successful runs when the program broke down again. The same old pattern. I test the model with different numbers, the program breaks down. I change back to the original numbers, and the program stays down.

Nevertheless, the program works beautifully on Excel worksheets. Thus, I should be able to finish my paper. My guess is that VBA is just a clumsy vehicle to do complex mathematical modelling. I chose VBA because its free if you've got Excel, and it produces nice output, both nice tables and charts. Gnu Octave is free, but it has very few support people and I can't figure out how to do tables. Matlab is good, but you've got to spend a bundle of money.

I've now completed all of the test runs I want to on Excel.
 
Last edited:
Upvote 0
so you have an excel spreadsheet that works fine - presumably you change A1 from 3 to 4 etc etc and look at the output. Tell us why you want to run a macro - is it because you want to make A1 many many different values. If we understand that we can still help you....
 
Upvote 0
The policy control variables are the production and consumption reduction rates. By changing the reduction rates, I change the abatement costs. By changing the abatement costs, I change output and every other variable. With higher output, there is higher energy usage, with higher energy usage, emissions increase. This leads to higher CO2 levels which leads to higher damages, which leads to lower survivability. That's the program in a nutshell. Controlling for reduction rates can be thought as carbon taxes or regulations. The values of these policy control variables are on the worksheet and not in the program until I call them up.

Since I want to preserve the values of each run, what I do is run the macro on sheets one. Then I copy and paste everything into a second macro changing the values of the policy control variables. I print the results in sheets 2 and so on. Currently, I have done 22 trial runs. So in the excel program I have 24 worksheets, 22 trial runs and two summary sheets.

I'm going with the excel program. However, I'm going to see if I can achieve the same results with the VBA program after I do a write up with the excel program. If I replicate the results, then I'll just say I did the simulations using Visual Basic. It would be kind of nice to be able to say I did it with a computer program than with just Excel. Economists have a bad reputation of always resorting to just Excel. I've pretty much abandoned the iteration program. My guess is that convex functions are crashing the program when the time horizon increases. To avoid this problem in excel, I use past variables as proxies for current variables. If the variable is a ratio, its probably a very good proxy. Since damages are moving slowly through time, damages lagged one period is probably a good proxy for current damages. I'm probably pretty accurate multiplying by a small growth factor say 1.00001. Nevertheless, I'm not really looking to get exact accurate numbers, but want to look at general trends and patterns.

The reason for proxy variables is that there is a simultaneity problem between output and damages. In excel its called a circular reference, which in a complex program is not solved by simply turning on the iteration function.
 
Last edited:
Upvote 0
One point of trouble is when the economy hits the critical temperature. This discontinuity has caused the program to crash in previous trial runs. However, this problem has been solved in the excel program. The critical temperature is the temperature where the runaway greenhouse effect occurs.
 
Upvote 0
The other night, the VBA program mysteriously started working. I got a bunch of trial runs in, but then noticed some mistakes ( a mistake which doesn't crash the program). Correcting the mistake on each trial run was too tedious, so I deleted all of the modules and worksheets to make the corrections on the base model. Unfortunately, the program crashed again, and the same pattern held up.

What I've noticed is that assignments of certain variables fail. That's where the division of zero error occurs. For example, I type

Dim x As Double
x = Sheets(1).Range("A23")
Debug.Print x

If you look at sheet 1, cell A23, the value is say 10, however, if you run the program and use Debug.Print x right underneath the variable assignment, the intermediate window will say 0. That's why I've been getting Time Error 5 or Division by zero errors.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top