A method of collecting data using COM object to create fan charts

'declare variable

Dim serev(48) As Variant


'use COM object to get standard errors from EViews model
serev(1) = app.Get("=eq_fiscal_rev_dom_1.@se")
serev(2) = app.Get("=eq_fiscal_rev_dom_2.@se")
serev(4) = app.Get("=eq_fiscal_rev_dom_4.@se")
serev(5) = app.Get("=eq_fiscal_rev_dom_5.@se")
serev(8) = app.Get("=eq_fiscal_rev_dom_8.@se")
serev(11) = app.Get("=eq_fiscal_rev_dom_11.@se")
serev(14) = app.Get("=eq_fiscal_rev_dom_14.@se")
serev(16) = app.Get("=eq_fiscal_rev_dom_16.@se")
serev(17) = app.Get("=eq_fiscal_rev_dom_17.@se")
serev(18) = app.Get("=eq_fiscal_rev_dom_18.@se")
serev(20) = app.Get("=eq_fiscal_rev_dom_20.@se")
serev(21) = app.Get("=eq_fiscal_rev_dom_21.@se")
serev(22) = app.Get("=eq_fiscal_rev_dom_22.@se")
serev(23) = app.Get("=eq_fiscal_rev_dom_23.@se")
serev(28) = app.Get("=eq_fiscal_rev_dom_28.@se")
serev(29) = app.Get("=eq_fiscal_rev_dom_29.@se")
serev(30) = app.Get("=eq_fiscal_rev_dom_30.@se")
serev(31) = app.Get("=eq_fiscal_rev_dom_31.@se")
serev(32) = app.Get("=eq_fiscal_rev_dom_32.@se")
serev(35) = app.Get("=eq_fiscal_rev_dom_35.@se")
serev(42) = app.Get("=eq_fiscal_rev_dom_42.@se")
serev(44) = app.Get("=eq_fiscal_rev_dom_44.@se")


'create range to place data
Dim revrange As Range

Set revrange = Worksheets("Stochastic Results").Range("B1:B49")

'place data into the range

revrange.Value = WorksheetFunction.Transpose(serev)

Within Excel we then use the NORMINV function to generate a set of ranges of the data, using the standard error from the regressions.

Posted 7:50pm on 7th December 2015