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

 

[Ed Song]

Finally, I am very sick and tired of this now. If I decide to make a last desperate attempt, I will pay Microsoft $500 to look at my program. However, I don't think I'm willing to pay even more money on this problem. I've already paid them over $150 for their failed attempts.

 

[Ed Song]

No. Its not on the worksheet, its in VBA.

 

[Michael M]

No, I meant is it a worksheet event in VBA ?
AND
what is the rest of the code, if any ??
we don't see the beginning OR end of the macro !
I don't profess to be able to assist, but it seems extremely odd that this can't be resolved, if it works on a worksheet, but not in VBA...

 

[Ed Song]

And I paid another expert $92 for his failed attempt. And subscribed to a forum for $30 per month for a year (a fee that I'm now disputing) for their nonattempt. So I may have to pay over one thousand dollars, with no guarantee that the problem will be solved.

 

[Michael M]

and you can have the same non solution here .......for free, but at least we're making an attempt !!!

 

[Ed Song]

OK if you want my code, here it is. The following code is the simplified version with no iterations. Note: Labor_Share may or may not be properly declared since that's the variable I've been testing out for the last few hours.

Option Explicit


'Base Model program. Emission control variables are set to zero. Time period length is one.


'This program is designed to simulate possible economic climate change incomes. It is not designed to make pin point _
point predictions, but to describe possible outcomes.




Sub Main()


'Declaration of Variables


'The general policy in declaring variables is that variables that are treated constants in this program are declared _
as single, and those that need to be calculated in VBA are declared as double. Counters are declared as long and _
not integers given the complicated nature of the calculations.


'Declaration of Parameters on the control panel on the Excel worksheets.


Dim Annual_GrowthRate As Double
Dim GrowthRate As Double
Dim Annual_Dep_K
Dim Dep_K As Single
Dim Discount_Factor As Single
Dim MU_Elasticity As Single
Dim Capital_Share As Single
Dim Labor_Share As Single
Dim Labor As Double
Dim Consumption_Share As Double
Dim Investment_Share As Double
Dim Government_Share As Single
Dim Productivity_0 As Single 'Also on the time series chart.
Dim AnnualOutput_0 As Single
Dim Prop_Consumption_Control As Single
Dim Exp_Consumption_Control As Single
Dim Prop_Production_Control As Single
Dim Exp_Production_Control As Single
Dim AbatementCost_Consumption As Double
Dim AbatmentCost_Production As Double
Dim RetentionRate As Single
Dim ClimateSensitivity As Single
Dim ClimateS_Lambda As Double
Dim Lambda As Double
Dim ClimateS_b1 As Double
Dim ClimateS_b2 As Double
Dim Temp_MaxC As Single
Dim Temp_SKI As Single
Dim CO2_EQ_Fraction As Single
Dim CO2_TransferRate As Single
Dim New_CO2_EQ As Double
Dim Constant_TDamages_Y As Single
Dim Exp_TDamages_Y As Single
Dim Constant_TDamages_K As Single
Dim Exp_TDamages_K As Single
Dim Constant_TDamages_U As Single
Dim Exp_TDamages_U As Single
Dim CO2_Equilibrium As Single
Dim EC_Ratio As Double
Dim EY_Ratio As Double
Dim EC_to_EY_Ratio As Double
Dim RetainedEmissions_C_Ratio As Double
Dim RetainedEmissions_Y_Ratio As Double
Dim Period_Length As Single
Dim Guessed_Critical_Temp As Single
Dim CO2_Ratio_GuessedTC As Double
Dim CO2_Level_GuessedCT As Double
Dim ReductionRate_Production As Single
Dim ReductionRate_Consumption As Single


'Declaration of initial period variables. Initial Productivity already declared.
Dim CapitalStock_0 As Double
Dim Output_0 As Double
Dim Consumption_0 As Double
Dim Investment_0 As Double
Dim Government_0 As Double
Dim DW_Loss_0 As Double
Dim EUsage_Consumption_0 As Double
Dim EUsage_Production_0 As Double
Dim EUsage_Total_0 As Double
Dim Total_Emissions_0 As Double
Dim Retained_Emissions_0 As Double
Dim CO2_Level_0 As Double
Dim New_CO2_EQ_0 As Double
Dim CO2_Transient_0 As Double
Dim Forcing_0 As Double
Dim Temp_Deviation_0 As Double
Dim Damage_Utility_0 As Double
Dim Damage_Prodution_0 As Double
Dim Damage_CapitalStock_0 As Double
Dim Damage_Composite_0 As Double
Dim Damage_Y_Composite_0 As Double
Dim Utility_Normalization As Double
Dim UtiltyIndex_0 As Double
Dim Survivability_0 As Double
Dim Welfare_0 As Double


'Declaration of period t (1) variables
Dim CapitalStock_1 As Double
Dim Output_1 As Double
Dim Consumption_1 As Double
Dim Investment_1 As Double
Dim Government_1 As Double
Dim Productivity_1 As Single
Dim AbatmentCost_Production_1 As Double
Dim DW_Loss_1 As Double
Dim EUsage_Consumption_1 As Double
Dim EUsage_Production_1 As Double
Dim EUsage_Total_1 As Double
Dim Total_Emissions_1 As Double
Dim Retained_Emissions_1 As Double
Dim Retained_C_Emissions_1 As Double
Dim Retained_Y_Emissions_1 As Double
Dim CO2_Level_1 As Double
Dim New_CO2_EQ_1 As Double
Dim CO2_Transient_1 As Double
Dim CO2_SKI As Double
Dim Forcing_1 As Double
Dim Temp_Deviation_1 As Double
Dim Damage_Utility_1 As Double
Dim Damage_Prodution_1 As Double
Dim Damage_CapitalStock_1 As Double
Dim Damage_Y_Composite_1 As Double
Dim Damage_Composite_1 As Double
Dim UtiltyIndex_1 As Double
Dim Survivability_1 As Double
Dim Welfare_1 As Double




'Intermediate variables will be declared just before they are used.


'Fill in remainder of control panel and initial values for variables. Note, that its much more efficient to _
calculate some control panel values with values from the time series.


'Steady State Growth Calculation
Annual_GrowthRate = Sheets(1).Range("G3")
Period_Length = Sheets(1).Range("H54")
GrowthRate = Annual_GrowthRate ^ Period_Length
Sheets(1).Cells(3, 8) = GrowthRate 'Print PeriodLength_GrowthRate in cell H3.


'Deprecation Rate Calculation for multiple periods
Annual_Dep_K = Sheets(1).Range("G4")
Dep_K = Annual_Dep_K ^ Period_Length
Sheets(1).Cells(4, 8) = Dep_K 'Print in cell H4.


'MU_Elasticity
MU_Elasticity = Sheets(1).Range("H5")


'Labor Share
Labor_Share = Sheets(1).Range("H7")


'Calculate Consumption Shares. It's a two step procedure requiring that Abatement cost of production be caclularted, too.


'Calculate Abatement Costs for Production or Output
Call AbatementCostsProduction(AbatmentCost_Production)

Sheets(1).Cells(62, 8) = AbatmentCost_Production 'Print Abatement Cost of Production in cell H62.


'Calculate Consumption_Share and Abatement Cost of Consumption. Abatement cost of production will be done later.
Call ConsumptionShare(AbatementCost_Consumption, Consumption_Share, DW_Loss_0)

Sheets(1).Cells(62, 8) = AbatementCost_Consumption 'Print in cell H62
Sheets(1).Cells(13, 8) = Consumption_Share 'Print in H13.

'Calculation of initial economic variable values and parameters.
Call InitialEconomicVariables(Output_0, Consumption_0, Investment_0, Government_0, Consumption_Share, Investment_Share, Government_Share)

Sheets(1).Cells(14, 8) = Investment_Share 'Print in H14.
Sheets(1).Cells(12, 8) = Output_0 'Print output for specified period length in cell H12.
Sheets(1).Cells(47, 8) = Consumption_0 'Print consumption in cell H47



'Calculation of Initial Emissions
Call InitialEmissions(CO2_Level_0, CO2_Equilibrium, CO2_EQ_Fraction, CO2_TransferRate, RetentionRate, _
Total_Emissions_0, Retained_Emissions_0)

Sheets(1).Cells(39, 8) = Retained_Emissions_0 ' Print retained emissions in cell H39
Sheets(1).Cells(40, 8) = Total_Emissions_0 'Print total emissions in cell H40.


'Calculation of Energy Usage
Call EnergyUsage(EUsage_Total_0, EUsage_Consumption_0, EUsage_Production_0)

'Print in cells H49:H50.
Sheets(1).Cells(49, 8) = EUsage_Consumption_0
Sheets(1).Cells(50, 8) = EUsage_Production_0


'Calculation of some Energy-Mass Ratios
Call EnergyMassRatios(EC_Ratio, EY_Ratio, EC_to_EY_Ratio, RetainedEmissions_C_Ratio, RetainedEmissions_Y_Ratio)

'Print these important ratios in cells H41:H45.
Sheets(1).Cells(41, 8) = EC_Ratio
Sheets(1).Cells(42, 8) = EY_Ratio
Sheets(1).Cells(43, 8) = EC_to_EY_Ratio
Sheets(1).Cells(44, 8) = RetainedEmissions_C_Ratio
Sheets(1).Cells(45, 8) = RetainedEmissions_Y_Ratio



'Calculation of Radiactive Forcing
Call Forcing(Forcing_0)


Sheets(1).Cells(51, 8) = Forcing_0 'Prints in H51

'Calculate the New Equilibrium level of CO2
Call NewEquilibrium(New_CO2_EQ_0, CO2_Transient_0)

'Calculate Temperature Deviations
Call Temperature(ClimateS_Lambda, ClimateS_b1, Temp_MaxC, CO2_SKI, _
Temp_SKI, ClimateS_b2, Temp_Deviation_0, CO2_Ratio_GuessedTC)

Sheets(1).Cells(58, 8) = CO2_Ratio_GuessedTC 'Print the critical Co2 concentration ratio for explosive temperature rise.
Sheets(1).Cells(59, 8) = CO2_SKI 'Print the CO2 level when the guessed SKI temperature is reached in cell H59.
Sheets(1).Cells(52, 8) = Temp_Deviation_0 'Print initial temperature deviation in cell H52.

'Calculate Damages
Call Damages(Constant_TDamages_U, Exp_TDamages_U, Damage_Utility_0, Constant_TDamages_Y, Exp_TDamages_Y, Damage_Prodution_0, _
Constant_TDamages_K, Exp_TDamages_K, Damage_CapitalStock_0, Damage_Y_Composite_0, Damage_Composite_0)

Sheets(1).Cells(53, 8) = Damage_Prodution_0 'Print in cell H53.

'Calculate the initial value of the Survivability Function.
Call Survivability(Survivability_0, Temp_MaxC)

Sheets(1).Cells(54, 8) = Survivability_0

'Calculate the initial world capital stock.
Call InitialCapitalStock(CapitalStock_0)

Sheets(1).Cells(48, 8) = CapitalStock_0 'Print Capital Stock in cell H48.

'Calculate Labor.
Call LaborSupply(Labor, Productivity_0, Capital_Share)

Sheets(1).Cells(8, 8) = Labor 'Print Labor in cell H8.

Call UtilityIndex(UtiltyIndex_0, Welfare_0, Utility_Normalization)

'Print the initial values of the time series chart from cells K2 to AL2.
Sheets(1).Range("K2:AL2").Value = _
Array(Productivity_0, CapitalStock_0, Output_0, Consumption_Share, Investment_Share, Government_Share, Consumption_0, Investment_0, _
Government_0, DW_Loss_0, EUsage_Total_0, EUsage_Total_0, EUsage_Consumption_0, Total_Emissions_0, Retained_Emissions_0, _
CO2_Level_0, CO2_Transient_0, New_CO2_EQ_0, Forcing_0, Temp_Deviation_0, Damage_Utility_0, Damage_Prodution_0, Damage_CapitalStock_0, _
Damage_Y_Composite_0, Damage_Composite_0, Survivability_0, UtiltyIndex_0, Welfare_0)

' End of Parameter and Initial Period Calculations

' Begin Future Value Double Loop Calculations


'Future values are now calculated. Since we know what investment was in the initial period, we can calculate the new amount of capital stock added. _
Unfortunately, we have a severe nonlinear problem because there is a simultaneity cause and effect problem with damages and survivability with _
output and other economic variables. One partial solution is to use past current ratios as instruments to current ratios. Last period ratios _
with respect to current survivability and damages to last period survivability and damages can be approximated by using that the lag of one period _
of that ratio since the changes in the ratio are smooth from period to period.


'But what about if there is no ratio to work with? Iteration techniques has already been tried without success because damages are just to convex _
for convergence to occur. On the other hand, linearization is problematic since the system of equations have already been linearized. The solution _
we will use will be to notice that damages doesn't change all that much from period to period. Thus, we can make output a function of past damages. _
Because this technique will underestimate damages, we multipy a small factor to past damages to get an estimate of present damages.


'Declare period 1 variables still not declared.
Dim RetainedEmissions_C_Ratio_1 As Double
Dim RetainedEmissions_Y_Ratio_1 As Double
Dim RawUtility_1 As Double
Dim Consumption_Share_1 As Double
Dim Government_Share_1 As Single
Dim Investment_Share_1 As Double


'Declare and set Instrumental Variables. Assume one percent of the losses occurred in the last year.
Dim Damage_Utility_minus1 As Double
Dim Survivability_minus1 As Double



Call SetInstruments(Damage_Utility_minus1, Survivability_minus1)


'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Begin Loop %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


'The outer loop is the time loop. After variables are calculated for a time period, the program exits the outer loop into the inner loop. When it _
exits the inner loop, it goes bact to the outer loop to the next period calculation, until 100 periods of calculations are done.


'Set Counter
Dim t As Long
t = 1


Do Until t = 101


'If the survivability function hits zero, the economy flatlines or dies. There is no output.


If Survivability_0 = 0 Then
Output_0 = 0
CapitalStock_1 = 0
DW_Loss_1 = 0
EUsage_Consumption_1 = 0
EUsage_Production_1 = 0
EUsage_Total_1 = 0

CO2_Level_1 = Retained_Emissions_1 + (CO2_EQ_Fraction * (CO2_Level_0 - CO2_Equilibrium)) + CO2_Equilibrium + _
((1 - CO2_EQ_Fraction) * (1 - CO2_TransferRate) * (CO2_Level_0 - CO2_Equilibrium)) + CO2_Equilibrium

CO2_Transient_1 = (1 - CO2_EQ_Fraction) * Retained_Emissions_1 + (1 - CO2_EQ_Fraction) * (1 - CO2_EQ_Fraction) * (CO2_Level_0 - CO2_Equilibrium)

New_CO2_EQ_1 = CO2_Level_1 - CO2_Transient_1

Damage_Utility_1 = 1
Damage_CapitalStock_1 = 1
Damage_Prodution_1 = 1
Damage_Y_Composite_1 = 1
Damage_Composite_1 = 1

'If the economy is still living, then proceed through the inner loop. Otherwise, go to the outer loop.


Else


'Reset Variables
Damage_CapitalStock_1 = Damage_CapitalStock_0


'Calculate the capital stock. Remember, damages to capital stock is treated like another form of depreciation except damages are not constant.
'Also calculate first period productivity.


CapitalStock_1 = Investment_0 + (1 - Dep_K) * ((1 - (1.00001 * Damage_CapitalStock_0)) ^ Period_Length) * CapitalStock_0
Productivity_1 = GrowthRate * Productivity_0

'Calculate Production Function
Output_1 = (GrowthRate * Output_0) + Productivity_1 * ((1 - ((1.00001 ^ Period_Length) * Damage_Prodution_0)) ^ Period_Length) * _
(1 - AbatmentCost_Production_1) * ((CapitalStock_1 - CapitalStock_0) ^ Capital_Share) * (Labor ^ Labor_Share)
Debug.Print Labor_Share

'Two step process to calculate period 1 consumption. Calculate the new consumption shares, then multiply by output. Abatement costs to consumption not needed since its a constant in this _
Program.

Consumption_Share_1 = 0.62 * (Survivability_minus1 * (1 - Damage_Utility_minus1) / (Survivability_0 * (1.00001 - Damage_Utility_0))) ^ _
(1 / (1 + MU_Elasticity))

Consumption_1 = Consumption_Share_1 * Output_1


'Calculate Investment share, Investment and government
Investment_Share_1 = 1 - Consumption_Share_1 - Government_Share
Investment_1 = Investment_Share_1 * Output_1
Government_1 = Government_Share * Output_1
DW_Loss_1 = Output_1 - Consumption_1 - Investment_1 - Government_1


'Energy balance calculations.
EUsage_Consumption_1 = EC_Ratio * Consumption_1
EUsage_Production_1 = EY_Ratio * Output_1
EUsage_Total_1 = EUsage_Consumption_1 + EUsage_Production_1
Retained_C_Emissions_1 = RetainedEmissions_C_Ratio * Consumption_1
Retained_Y_Emissions_1 = RetainedEmissions_Y_Ratio * Output_1
Retained_Emissions_1 = Retained_C_Emissions_1 + Retained_Y_Emissions_1
Total_Emissions_1 = Retained_Emissions_1 / RetentionRate

'Calculate CO2 levels
CO2_Level_1 = Retained_Emissions_1 + (CO2_EQ_Fraction * (1 - CO2_TransferRate) * (CO2_Level_0 - CO2_Equilibrium)) + _
((1 - CO2_EQ_Fraction) * (1 - CO2_TransferRate) * (CO2_Level_0 - CO2_Equilibrium)) + CO2_Equilibrium
CO2_Transient_1 = (1 - CO2_EQ_Fraction) * Retained_Emissions_1 + (1 - CO2_EQ_Fraction) * (1 - CO2_TransferRate) * (CO2_Level_0 - CO2_Equilibrium)


New_CO2_EQ_1 = CO2_Level_1 - CO2_Transient_1

'New Temperature Deviation
Dim Temp_Off_1 As Double
Dim TC_Actual As Single
Dim Switch As Integer

TC_Actual = Sheets(1).Range("H60")

'Calculate new Forcing.
Forcing_1 = 3.35 * (Log(1 + 1.2 * CO2_Level_1 + 0.005 * CO2_Level_1 ^ 2 + 1.4 * 10 ^ (-6) * CO2_Level_1 ^ 3) - _
Log(1 + 1.2 * CO2_Equilibrium + 0.005 * CO2_Equilibrium ^ 2 + 1.4 * 10 ^ (-6) * CO2_Equilibrium ^ 3))

Temp_Off_1 = ClimateS_Lambda * Forcing_1
If Temp_Off_1 < TC_Actual Then
Switch = 0
Else
Switch = 1
End If

Temp_Deviation_1 = Temp_Off_1 + ClimateS_b2 * Switch * (CO2_Level_1 / CO2_Equilibrium)


'Calulate Damages
Damage_Utility_1 = 1 - (1 / (1 + Constant_TDamages_U * Temp_Deviation_1 + Exp_TDamages_U * Temp_Deviation_1 ^ 2))
Damage_Prodution_1 = 1 - (1 / (1 + Constant_TDamages_Y * Temp_Deviation_1 + Exp_TDamages_Y * Temp_Deviation_1 ^ 2))
Damage_CapitalStock_1 = 1 - (1 / (1 + Constant_TDamages_K * Temp_Deviation_1 + Exp_TDamages_K * Temp_Deviation_1 ^ 2))
Damage_Y_Composite_1 = 1 - (1 - Damage_Prodution_1) * (1 - Damage_CapitalStock_1) ^ Capital_Share
Damage_Composite_1 = Damage_Utility_1 + (1 - Damage_Utility_1) * Damage_Utility_1 * (Damage_Y_Composite_1 ^ MU_Elasticity)

'Utility and welfare calculations


'Survivability Probability

Survivability_1 = 1 - (Temp_Deviation_1 ^ 2 / (Temp_MaxC ^ 2 + 1.96 * (Temp_MaxC - Temp_Deviation_1) ^ 2))

If Survivability_1 <= 0 Then
Temp_Deviation_1 = Temp_SKI
Survivability_1 = 0
End If

'Utility Index
Dim Raw_Utility_1 As Double
MU_Elasticity = Sheets(1).Range("H5")
Discount_Factor = Sheets(1).Range("H64")

Raw_Utility_1 = (Survivability_1 * (Consumption_1 ^ (1 + MU_Elasticity)) / (1 + MU_Elasticity))
UtiltyIndex_1 = Utility_Normalization * Raw_Utility_1

'Welfare
Welfare_1 = Welfare_0 + (Discount_Factor ^ Period_Length) * UtiltyIndex_1

'Print Output.
Sheets(1).Range("K" & 2 + t, "AL" & 2 + t).Value = _
Array(Productivity_1, CapitalStock_1, Output_1, Consumption_Share_1, Investment_Share_1, Government_Share, Consumption_1, Investment_1, _
Government_1, DW_Loss_1, EUsage_Total_1, EUsage_Total_1, EUsage_Consumption_1, Total_Emissions_1, Retained_Emissions_1, _
CO2_Level_1, CO2_Transient_1, New_CO2_EQ_1, Forcing_1, Temp_Deviation_1, Damage_Utility_1, Damage_Prodution_1, Damage_CapitalStock_1, _
Damage_Y_Composite_1, Damage_Composite_1, Survivability_1, UtiltyIndex_1, Welfare_1)

'Reset stock variable and other variables for iteration purposes.
Damage_CapitalStock_0 = Damage_CapitalStock_1
Damage_Utility_0 = Damage_Utility_1
Damage_Prodution_0 = Damage_Prodution_1
Investment_0 = Investment_1
CapitalStock_0 = CapitalStock_1
Output_0 = Output_1
CO2_Level_0 = CO2_Level_1
Welfare_0 = Welfare_1
Survivability_1 = Survivability_0
Survivability_0 = Survivability_minus1
Productivity_0 = Productivity_1

End If

t = t + 1


Loop
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% End Loop %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%






End Sub




Sub AbatementCostsProduction(AbatementCostY As Double)


'Declare and set local variable
Dim ReductionRateY As Single
Dim Prop_YControl As Single
Dim Exp_YControl As Single


ReductionRateY = Sheets(1).Range("H66")
Prop_YControl = Sheets(1).Range("H19")
Exp_YControl = Sheets(1).Range("H20")


'Calculate Abatement Cost of consumption
AbatementCostY = Prop_YControl * ReductionRateY ^ Exp_YControl


End Sub




Sub ConsumptionShare(AbatementCostC As Double, C_Share As Double, DWLoss As Double)


'Declare and set local variable
Dim ReductionRateC As Single
Dim Prop_CControl As Single
Dim Exp_CControl As Single
Dim MU_Elast As Single
Dim AbatementCostY As Double


AbatementCostY = Sheets(1).Range("H62")
ReductionRateC = Sheets(1).Range("H65")
Prop_CControl = Sheets(1).Range("H17")
Exp_CControl = Sheets(1).Range("H18")


'Calculate Abatement Cost of consumption
AbatementCostC = Prop_CControl * ReductionRateC ^ Exp_CControl
C_Share = 0.62 * ((1 - AbatementCostC) / (1 - AbatementCostY)) ^ (1 / (MU_Elast - 1))
DWLoss = C_Share * AbatementCostC


End Sub


Sub InitialEconomicVariables(Output As Double, Consumption As Double, Investment As Double, Government As Double, C_Share As Double, _
I_Share As Double, G_Share As Single)




Dim AnnualOutput As Double
Dim PeriodLength As Single
Dim GrowthRate As Double


AnnualOutput = Sheets(1).Range("G12")
PeriodLength = Sheets(1).Range("H56")
GrowthRate = Sheets(1).Range("H3")
C_Share = Sheets(1).Range("H13")
G_Share = Sheets(1).Range("H15")
I_Share = 1 - C_Share - G_Share






Output = AnnualOutput + (PeriodLength - 1) * GrowthRate * AnnualOutput
Consumption = C_Share * Output
Investment = I_Share * Output
Government = G_Share * Output


End Sub


Sub InitialEmissions(CO2 As Double, CO2_EQ As Single, CO2_EQ_Frac As Single, CO2_TransferRte As Single, RetentionRte As Single, _
TotalEmissions As Double, RetainedEmissions As Double)


'We have data on the net emissions of C)2 into the atmosphere, but not the actual gross quantity. This subroutine calculates the gross quantity _
of CO2 into the atmosphere.


CO2_EQ = Sheets(1).Range("H37")
CO2 = Sheets(1).Range("H38")
CO2_EQ_Frac = Sheets(1).Range("H25")
CO2_TransferRte = Sheets(1).Range("H27")
RetentionRte = Sheets(1).Range("H23")


RetainedEmissions = (1 - CO2_EQ_Frac) * CO2_TransferRte * (CO2 - 2.11 - CO2_EQ) + 2.11
TotalEmissions = RetainedEmissions / RetentionRte


End Sub


Sub EnergyUsage(TotalE As Double, ConsumptionE As Double, ProductionE As Double)


'Declare local variable
Dim TotalEmissions As Double


TotalEmissions = Sheets(1).Range("H40")




'There are 3.539 ppmv of CO2 emitted for every 1 TW-Yr of energy usage. Also convert to GWatt-Hrs by multiplying by 1000. I estimate that 60% _
of energy usage comes from production and 40% from consumption.


TotalE = (TotalEmissions / 3.539) * 1000
ConsumptionE = TotalE * 0.4
ProductionE = TotalE * 0.6


End Sub


Sub EnergyMassRatios(ECRatio As Double, EYRatio As Double, EC_EY_Ratio As Double, EmissionsC_Ratio As Double, EmissionsY_Ratio As Double)


'This subroutine calculates some Mass-Energy ratios that are necessary into converting energy usage into CO2 emissions.




'Declare local Variables
Dim TotalE As Double
Dim ConsumptionE As Double
Dim ProductionE As Double
Dim Consumption As Double
Dim Output As Double
Dim CO2_EQ_Frac As Double
Dim C_Share As Single
Dim RetainedEmissions As Double


'Energy to Consumption and Production ratios calculations.
Output = Sheets(1).Range("H12")
Consumption = Sheets(1).Range("H47")
ConsumptionE = Sheets(1).Range("H49")
ProductionE = Sheets(1).Range("H50")
C_Share = Sheets(1).Range("H13")
RetainedEmissions = Sheets(1).Range("H39")

ECRatio = ConsumptionE / Consumption
EYRatio = ProductionE / Output

'Calculate EC/EY ratio. In the initial period, according to the data C = .62Y.
EC_EY_Ratio = C_Share * (ECRatio / EYRatio)


'Retained Atmospheric CO2 emissions to consumption and production ratios. Also denoted as gamma(c) and gamma in this research.


RetainedEmissions = Sheets(1).Range("H39")
EmissionsC_Ratio = 0.4 * RetainedEmissions / Consumption
EmissionsY_Ratio = 0.6 * RetainedEmissions / Output




End Sub


Sub Forcing(Forcing As Double)


'Declare and set local variables.
Dim CO2 As Double
Dim CO2_EQ As Double


CO2 = Sheets(1).Range("H38")
CO2_EQ = Sheets(1).Range("H37")


'Calculate initial forcing.
Forcing = 3.35 * (Log(1 + 1.2 * CO2 + 0.005 * CO2 ^ 2 + 1.4 * 10 ^ (-6) * CO2 ^ 3) - _
Log(1 + 1.2 * CO2_EQ + 0.005 * CO2_EQ ^ 2 + 1.4 * 10 ^ (-6) * CO2_EQ ^ 3))


End Sub


Sub NewEquilibrium(NewEquilibrium As Double, TransientCO2 As Double)


'Declare and set local variables.
Dim CO2 As Double
Dim CO2_EQ As Single
Dim CO2_EQ_Fraction As Double


CO2 = Sheets(1).Range("H38")
CO2_EQ = Sheets(1).Range("H37")
CO2_EQ_Fraction = Sheets(1).Range("H25")


'Calculate the new long CO2 concentration level


NewEquilibrium = CO2_EQ_Fraction * (CO2 - CO2_EQ) + CO2_EQ
TransientCO2 = CO2 - NewEquilibrium
End Sub


Sub Temperature(CL_Lambda As Double, b1 As Double, TC_Max As Single, CO2SKI As Double, _
SKITemp As Single, b2 As Double, Temp As Double, CO2RatioC As Double)

'Declare and set local variables
Dim ClimateS As Single
Dim CO2 As Single
Dim CO2_EQ As Single
Dim CO2_RatioC As Double
Dim Forcing As Double
Dim Lambda As Double


ClimateS = Sheets(1).Range("H22")
CO2 = Sheets(1).Range("H38")
CO2_EQ = Sheets(1).Range("H37")
TC_Max = Sheets(1).Range("H57")
Forcing = Sheets(1).Range("H51")


'Calculate Proportionality Constant
CL_Lambda = ClimateS / 3.35 * (Log(1 + 1.2 * CO2 + 0.005 * CO2 ^ 2 + 1.4 * 10 ^ (-6) * CO2 ^ 3) - _
Log(1 + 1.2 * CO2_EQ + 0.005 * CO2_EQ ^ 2 + 1.4 * 10 ^ (-6) * CO2_EQ ^ 3))

'Calculate Climate Sensitivity Parameter b1
b1 = ClimateS * CL_Lambda


'Approximation of SKI concentration ratio and concentration.
Lambda = ClimateS / (5.35 * Log(2))


CO2RatioC = Exp(TC_Max / (Lambda * 5.35))
CO2SKI = CO2_EQ * CO2RatioC


'Calculate Climate Sensitivity parameter b2
'Note: some simplications were made to calculate this parameter. To caculate the CO2 concentration where the critical temperature was reached, _
I assumed that the first order approximation of radioactive forcing was valid. Thus forcing is propoertional to the natural log of the _
current temperature and initial equilibrium temperature. Note also we have been using the third order approximation in our calculations before.

SKITemp = Sheets(1).Range("H61")
b2 = (SKITemp - 5.35 * Lambda * Log(CO2RatioC)) / CO2RatioC

'Calculate initial temperature
Temp = CL_Lambda * Forcing


End Sub




Sub Damages(Prop_TempU As Single, Exp_TempU As Single, DamagesU As Double, Prop_TempY As Single, Exp_TempY As Single, DamagesY As Double, _
Prop_TempK As Single, Exp_TempK As Single, DamagesK As Double, DamagesTotalY As Double, DamagesTotalU As Double)


'Declare and set local variables.
Dim Temp As Double
Dim K_Share As Double
Dim MU_Elast As Double


Temp = Sheets(1).Range("H52")
K_Share = Sheets(1).Range("H6")
MU_Elast = Sheets(1).Range("H5")


'Utility Damage calculation.
Prop_TempU = Sheets(1).Range("H34")
Exp_TempU = Sheets(1).Range("H35")
DamagesU = 1 - (1 / (1 + Prop_TempU * Temp + Exp_TempU * Temp ^ 2))


'Production Damage calculation.
Prop_TempY = Sheets(1).Range("H30")
Exp_TempY = Sheets(1).Range("H31")
DamagesY = 1 - (1 / (1 + Prop_TempY * Temp + Exp_TempY * Temp ^ 2))


'Capital Stock Damage calculation.
Prop_TempK = Sheets(1).Range("H32")
Exp_TempK = Sheets(1).Range("H33")
DamagesK = 1 - (1 / (1 + Prop_TempK * Temp + Exp_TempK * Temp ^ 2))


'Composite Damage calculations.
DamagesTotalY = 1 - (1 - DamagesY) * (1 - DamagesK) ^ K_Share
DamagesTotalU = DamagesU + (1 - DamagesU) * DamagesU * (DamagesTotalY ^ MU_Elast)


End Sub




Sub Survivability(Survive As Double, TC_Max As Single)


'Declare and set local variables
Dim Temp As Double


TC_Max = Sheets(1).Range("H57")
Temp = Sheets(1).Range("H52")


Survive = 1 - (Temp ^ 2 / (TC_Max ^ 2 + 1.96 * (TC_Max - Temp) ^ 2))




End Sub


Sub InitialCapitalStock(K)


'Calculate Initial Capital Stock. This is very controversial and is a reason why thre is skepticsm of the Neo-Classical model. _
Nevertheless, we use Nordhaus estimate of 97.3 trillion dollars as the approximate world capital stock, that includes natural resources. _
Nordhaus uses purchasing power parity PPP and is valued in 2005 doallars. I use the exchange rate value and 2014 dollars. Thus, I must convert _
the units by multiplying Nordhaus value by the ratio of world GDP exchange rate to GDP PPP multiplying the ratio of GDP in 2014 dollars to GDP _
in 2005 dollars.


K = 97.3 * (78.22 / 107.5) * (78.22 / 55.34)


End Sub


Sub LaborSupply(L As Double, Productivity As Single, Share_K As Single)


'Declare and set local variables.
Dim Output As Double
Dim AbatementCostsY As Double
Dim DamagesY As Double
Dim K As Double
Dim Share_L As Single


Output = Sheets(1).Range("H12")
AbatementCostsY = Sheets(1).Range("H62")
DamagesY = Sheets(1).Range("H53")
Productivity = Sheets(1).Range("H11")
K = Sheets(1).Range("H48")
Share_K = Sheets(1).Range("H6")
Share_L = Sheets(1).Range("H7")


'Calculate Inflexible Labor Supply.
L = (Output / ((1 - AbatementCostsY) * (1 - DamagesY) * Productivity * K ^ Share_K)) ^ (1 / Share_L)


End Sub


Sub UtilityIndex(Utility As Double, Welfare As Double, U_Normalization As Double)


'Declare and set local variables.
Dim RawUtility As Double
Dim Survive As Double
Dim Consumption As Double
Dim MU_Elast As Single


Survive = Sheets(1).Range("H54")
Consumption = Sheets(1).Range("H47")
MU_Elast = Sheets(1).Range("H5")


'Compute Variables
RawUtility = (Survive * (Consumption ^ (1 + MU_Elast)) / (1 + MU_Elast))
U_Normalization = 100 / RawUtility
Utility = U_Normalization * RawUtility
Welfare = Utility


End Sub


Sub SetInstruments(PastU As Double, PastS As Double)


'Declare and set local variable
Dim PeriodLength As Single
Dim U As Double
Dim S As Double


PeriodLength = Sheets(1).Range("H56")
U = Sheets(1).Range("AE2")
S = Sheets(1).Range("AJ2")


PastU = U / 0.99 ^ (PeriodLength - 1)
PastS = S / 0.99 ^ (PeriodLength - 1)








End Sub

 

[Ed Song]

Thanks.

 

[Ed Song]

Also, I don't know whether it works on a worksheet. But that's what I plan to do in the next couple of days. Like I said, Excel should be able to solve the problem. I got rid of the simultaneity problem by making the assumption that the last period variables are good approximations for the current variables. I did adjust the values by a slight adjustment factor.

 

[oldbrewer]

My suggestion is now to state in words only what data you have, and what you want to do with it. People on here will no doubt ask for clarifications but soon we will get a PERFECT PROBLEM STATEMENT. We will then debate the best way to achieve your needs.

I guess you are frustrated but do not vent it on here. Here there are experts who earn their living by working with excel, but they give valuable information for free on here. Be thankful and grateful.

 

[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))
g	growth factor
p	period length
o	output t0
f	factor productivity
d	damages production
a	abatementcostproduction
c	capitalstockt1
cc	capital stockt0
s	sharekcapital
l	labort0
z	sharelabour
		output_t1=g^p…. *…. (o+(f*(1-d)*(1-a))……. * ( (c-cc))^s ……... * ……. l^Z
			is this what you mean to do ?

<colgroup><col span="12"></colgroup><tbody>
</tbody>