Hi,

I am working on a simulation and am struggling with filling an array.
Some background
* As first step I made a simulation of 1000 loans
* As a next step I have made a calculation sheet (Sim_bond_calc) to
calculate cash flows for each mortgage (the number of cash flows is
variable (parameter ‘i’ in my macro) / max 121 rows per loan). The
sheet uses the simulated loans as a starting point (parameter ‘h’ in
my macro)
* Now I am trying to create a macro that performs the cash flow
calculation for each simulated loan and stores the output in separate
tab. Since the number of rows may grow to approximately 121.000 I have
chosen to use an array function.

I am struggling with filling the array properly:
• First I have tried to loop through the calculation sheet and write
the results to the array. This is shown in Macro 1 below. The macro
does only store the results of the last loan (number 1000)
• Next I have tried to copy all cash flows from the calculation sheet
to the array (macro 2) but whatever I try I get a run-time ‘1004’ –
Method ‘Range of object’_Global’ failed error.

I hope someone can help me!

Kind regards,

Paul


MACRO 1

Sub Sim_Cash_Flow_Projection()
Dim CellsDown As Long, CellsAcross As Integer
Dim h As Long, i As Long, j As Integer
Dim TempArray() As Variant
Dim TheRange As Range
Dim CurVal As Variant
Dim Row_ As Integer
Dim Cnt As Integer
Dim CurVal1 As Integer
Dim RowCF As Integer


'Clear previous results
Application.Goto Reference:="SIm_Bond_Range"
Selection.ClearContents

' Get dimensions
CellsDown = Sheets("Sim_loan_DB").Range("B2").Value * 121
CellsAcross = Sheets("Sim_loan_DB").Range("B4").Value

RowCF = Sheets("Sim_loan_DB").Range("B6").Value

' Redimension temporary array
ReDim TempArray(0 To CellsDown, 0 To CellsAcross)

' set worksheet range
Set TheRange = Range(Cells(2, 1), Cells(CellsDown, CellsAcross))

' Fill the temporary array

Application.ScreenUpdating = False


Sheets("Sim_bond_calc").Select
CurVal = 26
CurVal1 = 1

For h = 1 To Sheets("Sim_loan_DB").Range("B2")
Sheets("Sim_bond_calc").Range("c10").Value = h
For i = 0 To Sheets("Sim_bond_calc").Range("c18")
For j = 0 To CellsAcross
TempArray(i, j) = Cells(CurVal + i, CurVal1 + j).Value
Next j
Next i
Next h

' Transfer temporary array to worksheet
TheRange.Value = TempArray

Application.ScreenUpdating = True

End Sub

MACRO 2
Sub Sim_Cash_Flow_Projection2()
Dim CellsDown As Long, CellsAcross As Integer
Dim h As Long, i As Long, j As Integer
Dim TempArray() As Variant
Dim TheRange As Range
Dim CurVal As Variant
Dim Row_ As Integer
Dim Cnt As Integer
Dim CurVal1 As Integer
Dim RowCF As Integer


'Clear previous results
Application.Goto Reference:="SIm_Bond_Range"
Selection.ClearContents

' Get dimensions
CellsDown = Sheets("Sim_loan_DB").Range("B2").Value * 121
CellsAcross = Sheets("Sim_loan_DB").Range("B4").Value

RowCF = Sheets("Sim_loan_DB").Range("B6").Value

' Redimension temporary array
ReDim TempArray(0 To CellsDown, 0 To CellsAcross)

' set worksheet range
Set TheRange = Range(Cells(2, 1), Cells(CellsDown, CellsAcross))

' Fill the temporary array

Application.ScreenUpdating = False


Sheets("Sim_bond_calc").Select

For h = 1 To Sheets("Sim_loan_DB").Range("B2")
Sheets("Sim_bond_calc").Range("c10").Value = h
TempArray() = Range(Cells(26,
1).Resize(Sheets("Sim_bond_calc").Range("c18"), 13)).Value
Next h

' Transfer temporary array to worksheet
TheRange.Value = TempArray

Application.ScreenUpdating = True

End Sub

Couple of things that jump out a

1. Can't ReDim multi-dim arrays.
2. Better to dim a variant and dump the range into it.

'Define the range
Set TheRange = Range(Cells(2, 1), Cells(CellsDown, CellsAcross))

'Dump the range into an array
Dim vaData As Variant '**Note no parenthesis used**
vaData = TheRange

'Dump the array back into the range
TheRange = vaData

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 25 apr, 11:48, GS wrote:
Couple of things that jump out a

* 1. *Can't ReDim multi-dim arrays.
* 2. *Better to dim a variant and dump the range into it.

* * * 'Define the range
* * * Set TheRange = Range(Cells(2, 1), Cells(CellsDown, CellsAcross))

* * * 'Dump the range into an array
* * * Dim vaData As Variant '**Note no parenthesis used**
* * * vaData = TheRange

* * * 'Dump the array back into the range
* * * TheRange = vaData

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry,

Thanks for your swift response. I think I understand your proposed
solution but it does solve all my issues. I can't fill the array in
one step since I need to calculate the cash flows on a loan by loan
basis and subsequently add them to the array . I have tried several
things but I haven't managed to fill the array properly (probably I
have to create an array for each cash flow and write that to the
'TempArray' I defined earlier but I wouldn't know how to do this).

In the mean-time I have created a macro that does work but it perform
terribly bad. Would you have a suggestion on how to achieve the same
result with an Array?

Thanks,

Paul

--- The macro

Sub Sim_CF_Projection()
Dim i As Integer

Application.Goto Reference:="SIm_Bond_Range"
Selection.ClearContents

Application.ScreenUpdating = False

For i = 1 To Sheets("Sim_loan_DB").Range("B2")
Sheets("Sim_bond_calc").Range("c10").Value = i

Sheets("Sim_bond_calc").Select
Range(Cells(26, 1), Cells(Sheets("Sim_bond_calc").Range("c18").Value +
25, 13)).Copy
Application.Goto Reference:="Final_Row"
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Next i

Application.ScreenUpdating = True

Sheets("Pivot").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh

End Sub



The problem is that I need a temporary array that needs to be filled
in multiple successive steps

On Apr 25, 11:33*am, Paul S wrote:
In the mean-time I have created a macro that does work
but it perform terribly bad. Would you have a suggestion
on how to achieve the same result with an Array?

If it performs "terribly bad", I wonder if that might be due to the
(automatic) calculations that are performed in the Excel worksheets
each iteration of the loop.

It would be helpful if you uploaded the Excel file to a file-sharing
website and post the URL (http://...) of the uploaded file in a
response here. Be sure to make the uploaded file shared or sharable.
See the list of free file-sharing websites below.

I presume that you do not use
Application.Calculation=xlCalculationManual because the Sim_bond_calc!
C18 and the range starting at Sim_bond_calc!A25 change each time
Sim_bond_calc!C10 is modified in the loop. Right?

Not much you can do about that, if that is the case.

(However, if your workbook has a lot irrelevant formulas that might be
costly, you might consider setting xlCalculationManual and using
Range(...).Calculate within the loop to calculation just what you need
to.)

If I interpret your implementation correctly, one of the following
macros might perform a little better, at least the VBA portion. The
difference between "v1" and "v2" is the elimination of the End(xlUp)
each iteration.

Note: I left the Range(...).Select statements in, albeit optimized.
Ordinarily, I would dispense with them as well, relying on With
statements instead. But I don't know if the relative difference
performance-wise, if any.

-----

The macros....

Sub Sim_CF_Projection_v1()
Dim i As Long, n As Long, k As Long
Dim v As Variant

Application.ScreenUpdating = False

Sheets("Sim_loan_DB").Select
Range("Sim_Bond_Range").ClearContents
n = Range("B2")

Sheets("Sim_bond_calc").Select
For i = 1 To n
Range("c10") = i
k = Range("c18")
v = Range(Cells(26, 1), Cells(k + 25, 13))
Range("Final_Row").End(xlUp).Offset(1, 0).Resize(k, 13) = v
Next i

Sheets("Pivot").Select
PivotTables("PivotTable1").PivotCache.Refresh
Application.ScreenUpdating = True
End Sub

'-----

Sub Sim_CF_Projection_v2()
Dim i As Long, n As Long, k As Long
Dim v As Variant
Dim j As Long, r As Range

Application.ScreenUpdating = False

Sheets("Sim_loan_DB").Select
Range("Sim_Bond_Range").ClearContents
n = Range("B2")

Sheets("Sim_bond_calc").Select
Set r = Range("Final_Row").End(xlUp)
j = 1
For i = 1 To n
Range("c10") = i
k = Range("c18")
v = Range(Cells(26, 1), Cells(k + 25, 13))
r.Offset(j).Resize(k, 13) = v
j = j + k
Next i

Sheets("Pivot").Select
PivotTables("PivotTable1").PivotCache.Refresh
Application.ScreenUpdating = True
End Sub

-----

Some free file-sharing websites that people suggest....

Windows Live Skydrive: http://skydrive.live.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidSha http://www.rapidshare.com
Box.Net: http://www.box.net/files

Ok, I was thinking to dump the source range into an array, work the
array istead of iterating cells in the worksheet, then dump the array
back into the worksheet.

I can see, though, where you might want to use another array to collect
your calculated cash flows. Working with arrays is way faster than
iterating ranges, and so I usually go with that approach.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 25 apr, 21:47, joeu2004 wrote:
On Apr 25, 11:33*am, Paul S wrote:

In the mean-time I have created a macro that does work
but it perform terribly bad. Would you have a suggestion
on how to achieve the same result with an Array?

If it performs "terribly bad", I wonder if that might be due to the
(automatic) calculations that are performed in the Excel worksheets
each iteration of the loop.

It would be helpful if you uploaded the Excel file to a file-sharing
website and post the URL (http://...) of the uploaded file in a
response here. *Be sure to make the uploaded file shared or sharable.
See the list of free file-sharing websites below.

I presume that you do not use
Application.Calculation=xlCalculationManual because the Sim_bond_calc!
C18 and the range starting at Sim_bond_calc!A25 change each time
Sim_bond_calc!C10 is modified in the loop. *Right?

Not much you can do about that, if that is the case.

(However, if your workbook has a lot irrelevant formulas that might be
costly, you might consider setting xlCalculationManual and using
Range(...).Calculate within the loop to calculation just what you need
to.)

If I interpret your implementation correctly, one of the following
macros might perform a little better, at least the VBA portion. *The
difference between "v1" and "v2" is the elimination of the End(xlUp)
each iteration.

Note: *I left the Range(...).Select statements in, albeit optimized.
Ordinarily, I would dispense with them as well, relying on With
statements instead. *But I don't know if the relative difference
performance-wise, if any.

-----

The macros....

Sub Sim_CF_Projection_v1()
Dim i As Long, n As Long, k As Long
Dim v As Variant

Application.ScreenUpdating = False

Sheets("Sim_loan_DB").Select
Range("Sim_Bond_Range").ClearContents
n = Range("B2")

Sheets("Sim_bond_calc").Select
For i = 1 To n
* *Range("c10") = i
* *k = Range("c18")
* *v = Range(Cells(26, 1), Cells(k + 25, 13))
* *Range("Final_Row").End(xlUp).Offset(1, 0).Resize(k, 13) = v
Next i

Sheets("Pivot").Select
PivotTables("PivotTable1").PivotCache.Refresh
Application.ScreenUpdating = True
End Sub

'-----

Sub Sim_CF_Projection_v2()
Dim i As Long, n As Long, k As Long
Dim v As Variant
Dim j As Long, r As Range

Application.ScreenUpdating = False

Sheets("Sim_loan_DB").Select
Range("Sim_Bond_Range").ClearContents
n = Range("B2")

Sheets("Sim_bond_calc").Select
Set r = Range("Final_Row").End(xlUp)
j = 1
For i = 1 To n
* *Range("c10") = i
* *k = Range("c18")
* *v = Range(Cells(26, 1), Cells(k + 25, 13))
* *r.Offset(j).Resize(k, 13) = v
* *j = j + k
Next i

Sheets("Pivot").Select
PivotTables("PivotTable1").PivotCache.Refresh
Application.ScreenUpdating = True
End Sub

-----

Some free file-sharing websites that people suggest....

Windows Live Skydrive:http://skydrive.live.com
MediaFihttp://www.mediafire.com
FileFactory:http://www.filefactory.com
FileSavr:http://www.filesavr.com
FileDropper:http://www.filedropper.com
RapidShahttp://www.rapidshare.com
Box.Net:http://www.box.net/files

Thanks for your extensive feedback, I have tried both macros and
especially the second one performs significantly better (approximately
twice as fast as the original one but it still takes about 30 seconds
for a simulation of 1000 loans). I am still very much interested in an
array solution (as you can probably see I am still a beginner in
VBA ..). I have uploaded the file to the following website
http://www.filedropper.com/exampleibnd

Thanks again,

Paul

On Apr 25, 11:47*pm, Paul S wrote:
I have tried both macros and especially the second one
performs significantly better [...]. I am still very
much interested in an array solution [...]. I have
uploaded the file to the following
websitehttp://www.filedropper.com/exampleibnd

I am unable to look at that file. I use XL2003, and the conversion
utility has trouble with it. One problem is: it has more than 65536
rows. Perhaps if you uploaded a simpler example, I can look at it.
But I don't know if that is why the conversion utility fails (with a
"send error" type internal failure).

But note that the macros I provided __are__ array solutions. The
assignment statement

v = Range(Cells(26, 1), Cells(k + 25, 13))

causes v, declared as Variant, to contain an array of dimensions 1 to
k by 1 to 13, independent of Option Base (default 0). See the
demonstration macros below.

AFAIK, that is the only way to read a range into a VBA array. We
cannot declare the indices and type of the array. And that limitation
seems appropriate since the range might contain a mix of types as well
as Excel errors.

It might be significantly better if you moved the Excel calculations
into your macro, setting xlCalculationManual. But I cannot comment on
that further without seeing the Excel file.

If you save the Excel file as xls (compatibility mode), that would
make it easier for me. Be sure to open the xls file yourself before
uploading to be sure that it represents what you want me to see.

On Apr 26, 9:39*am, joeu2004 wrote:
See the demonstration macros below.

In case you cannot see them ;-), here they are ....

Sub doFirst()
'**********
'* select a new sheet before using.
'* write to range.
'* note that we can __write__ a typed declared array
'* to a range, but we cannot __read__ a range into a
'* typed declared array
'**********
Dim a(1 To 9, 1 To 3) As Long
Dim i As Long, j As Long
For i = 1 To 9: For j = 1 To 3
a(i, j) = 10 * i + j
Next: Next
Range("a1").Resize(9, 3) = a
End Sub

Sub doSecond()
Dim i As Long, j As Long, s As String, a(1, 1) As Long
'**********
'* read range into v; becomes v()
'**********
Dim v As Variant
v = Range("a1").Resize(9, 3)
s = "option base " & LBound(a, 1) & Chr(10) & _
"v(" & LBound(v, 1) & " to " & UBound(v, 1) & _
"," & LBound(v, 2) & " to " & UBound(v, 2) & ")"
For i = 1 To 9: For j = 1 To 3
If j = 1 Then s = s & Chr(10) & v(i, 1) _
Else s = s & " " & v(i, j)
Next: Next
MsgBox s
End Sub

On Apr 26, 9:39*am, joeu2004 wrote:
I am unable to look at that file.

Someone changed the file (defined name ranges) so that I could
download it and open it in XL2003.

I made some small changes. See the New_Simulation and Demo modules in
the VBA project in the file at http://www.box.net/shared/kuccspy987 .

Note that I did not test the New_Simulation changes. I did not run
the simulation because the XL2003 conversion is incomplete. For
example, there is a #REF error in column I of the Sim_bond_calc
worksheet. I suspect it referenced a row beyond 65536, the XL2003
limit. Also, I think the pivot tables might not work.

Although you cannot fully use the XL2003 file, you can still copy-and-
paste from the VBA modules.

The following are some observations that might be helpful.

The key change in the New_Simulation module is the use of a variant
array to read Sim_bond_db!A9:M9 in one statement instead of a loop.

Execute the demo1 and demo2 procedures to see the performance
difference between reading an entire range into an array in one
statement v. looping. On my computer, the difference is almost 2x.

I also made a number of other changes to clean up the implementation.

Also note that I replaced Integer types with Long types. There is no
longer any performance benefit from using Integer instead of Long.
More importantly, using Long instead of Integer is likely to avoid
programming failures because Integer is limited to 32767. This is
especially important in your case, since some of your Integer
variables hold row numbers, which can exceed 32767 by design.

However, I suspect those changes will __not__ significantly improve
the performance.

I suspect a major source of performance degradation is due to the
plethora of volatile functions -- OFFSET as well as RAND and
RANDBETWEEN. I count nearly 1100 such statements. These cause those
cells and their dependent cells to be recalculated every time any cell
in the workbook is "edited", which includes write to them from VBA.

Just how much of the volatility is needed for your simulation, I did
not try to understand. Certainly part of your design depends on it.
But ordinarily, I would prefer to move at least some of the random
calculations into VBA functions or macros to avoid unnecessary
recalculations.

Also, you might be able to replace OFFSET with INDEX, which is not
volatile. Again, I did not try to understand your design in
sufficient detail to know for sure.

A minor nitpick.... There is no point in using the CONCATENATE
function. For example, =CONCATENATE(A1,"_",B1) can be written as
=A1&"_"&B1. However, I suspect there is no performance impact in
using CONCATENATE. There are relatively few such formulas. And I
suspect (but I don't really know) that Excel implements CONCATENATE as
an operator, not a bona fide function.

I hope these comments and the examples are helpful. Good luck!

On 27 apr, 11:12, joeu2004 wrote:
On Apr 26, 9:39*am, joeu2004 wrote:

I am unable to look at that file.

Someone changed the file (defined name ranges) so that I could
download it and open it in XL2003.

I made some small changes. *See the New_Simulation and Demo modules in
the VBA project in the file athttp://www.box.net/shared/kuccspy987.

Note that I did not test the New_Simulation changes. *I did not run
the simulation because the XL2003 conversion is incomplete. For
example, there is a #REF error in column I of the Sim_bond_calc
worksheet. *I suspect it referenced a row beyond 65536, the XL2003
limit. *Also, I think the pivot tables might not work.

Although you cannot fully use the XL2003 file, you can still copy-and-
paste from the VBA modules.

The following are some observations that might be helpful.

The key change in the New_Simulation module is the use of a variantarrayto read Sim_bond_db!A9:M9 in one statement instead of a loop.

Execute the demo1 and demo2 procedures to see the performance
difference between reading an entire range into anarrayin one
statement v. looping. *On my computer, the difference is almost 2x.

I also made a number of other changes to clean up the implementation.

Also note that I replaced Integer types with Long types. *There is no
longer any performance benefit from using Integer instead of Long.
More importantly, using Long instead of Integer is likely to avoid
programming failures because Integer is limited to 32767. *This is
especially important in your case, since some of your Integer
variables hold row numbers, which can exceed 32767 by design.

However, I suspect those changes will __not__ significantly improve
the performance.

I suspect a major source of performance degradation is due to the
plethora of volatile functions -- OFFSET as well as RAND and
RANDBETWEEN. *I count nearly 1100 such statements. *These cause those
cells and their dependent cells to be recalculated every time any cell
in the workbook is "edited", which includes write to them from VBA.

Just how much of the volatility is needed for your simulation, I did
not try to understand. *Certainly part of your design depends on it.
But ordinarily, I would prefer to move at least some of the random
calculations into VBA functions or macros to avoid unnecessary
recalculations.

Also, you might be able to replace OFFSET with INDEX, which is not
volatile. *Again, I did not try to understand your design in
sufficient detail to know for sure.

A minor nitpick.... *There is no point in using the CONCATENATE
function. *For example, =CONCATENATE(A1,"_",B1) can be written as
=A1&"_"&B1. *However, I suspect there is no performance impact in
using CONCATENATE. *There are relatively few such formulas. *And I
suspect (but I don't really know) that Excel implements CONCATENATE as
an operator, not a bona fide function.

I hope these comments and the examples are helpful. *Good luck!

Thanks again for all all your very helpful feedback! I am getting the
idea and am currently trying to use your logic for the second macro
(sim_cash_flow_projection). I have one more question on your version
of the sim_mortgage_production macro: you fill the TempArray by
looping through all the columns. Isn't it easier and faster to add the
entire row in step to the array?

Kind regards,

Paul

On Apr 27, 9:13*am, Paul S wrote:
I have one more question on your version of the
sim_mortgage_production macro: you fill the TempArray
by looping through all the columns. Isn't it easier
and faster to add the entire row in step to the array?

See the demo macros below.

I think you have something like doit1 in mind. I, too, thought that
should work. It does not produce an error. But it also does not
produce the desired result.

(A defect? I am using XL2003 with VBA 6.5. YMMV with XL2007 and
later. Or perhaps another reader knows how to tweak doit1 so that it
will work.)

You might consider morphing doit1 into doit2. But that defeats the
benefit of accumulating all of the changes each iteration into a
single variant array.

Instead, I resorted to doit3: reading a range into one variant,
looping to copy that variant into a variant array, then writing the
entire second variant array to a range in one statement.

As you should see, the performance of doit3 is significantly better
than doit2 -- about 1.7x on my computer (YMMV).

But if doit1 works on your version of XL/VBA, by all means use it. It
should perform at least as well as doit3, if not somewhat better.

The macros....

'**********
'repeat each macro at least twice.
'first-time performance is often misleading.
'usually includes first-time VBA overhead for
'each code path (delayed compilation?). but
'sometimes uncharacteristically short(!)
'**********

Option Explicit

Public Declare Function QueryPerformanceFrequency _
Lib "kernel32" _
(ByRef freq As Currency) As Long
Public Declare Function QueryPerformanceCounter _
Lib "kernel32" _
(ByRef cnt As Currency) As Long

Private freq As Currency, df As Double

Sub doit1()
Dim t(1 To 10) '<----- array of variant arrays
Dim i As Long, s As String, et As Currency
'ensure we do not overwrite the wrong worksheet
Sheets("test").Select
Range("a:z").Clear
Range("a1:e1") = Array(1, 2, 3, 4, 5)
et = myTimer
For i = 1 To 10
Range("a1") = i
t(i) = Range("a1:e1")
s = s & Chr(10) & i & ": " & _
LBound(t(i), 1) & " to " & UBound(t(i), 1) & _
", " & _
LBound(t(i), 2) & " to " & UBound(t(i), 2)
Next
Range("a3").Resize(10, 5) = t
et = myTimer - et
s = s & Chr(10) & _
Format(myElapsedTime(et) * 1000, "0.000000 msec")
MsgBox s
End Sub

Sub doit2()
Dim t(1 To 10) '<----- array of variant arrays
Dim i As Long, s As String, et As Currency
'ensure we do not overwrite the wrong worksheet
Sheets("test").Select
Range("a:z").Clear
Range("a1:e1") = Array(1, 2, 3, 4, 5)
et = myTimer
For i = 1 To 10
Range("a1") = i
t(i) = Range("a1:e1")
s = s & Chr(10) & i & ": " & _
LBound(t(i), 1) & " to " & UBound(t(i), 1) & _
", " & _
LBound(t(i), 2) & " to " & UBound(t(i), 2)
Next
For i = 1 To 10
Range("a2:e2").Offset(i) = t(i)
Next i
et = myTimer - et
s = s & Chr(10) & _
Format(myElapsedTime(et) * 1000, "0.000000 msec")
MsgBox s
End Sub

Sub doit3()
'could Dim t(...,...) as Long in this case; better
Dim t(1 To 10, 1 To 5) '<----- array of variants
Dim i As Long, s As String, et As Currency
Dim j As Long, t2 '<----- "as Variant" implied
'ensure we do not overwrite the wrong worksheet
Sheets("test").Select
Range("a:z").Clear
Range("a1:e1") = Array(1, 2, 3, 4, 5)
et = myTimer
For i = 1 To 10
Range("a1") = i
t2 = Range("a1:e1")
For j = 1 To 5: t(i, j) = t2(1, j): Next
'retained for apples-to-apples performance comparison
s = s & Chr(10) & i & ": " & _
LBound(t, 1) & " to " & UBound(t, 1) & _
", " & _
LBound(t, 2) & " to " & UBound(t, 2)
Next
Range("a3").Resize(10, 5) = t
et = myTimer - et
s = s & Chr(10) & _
Format(myElapsedTime(et) * 1000, "0.000000 msec")
MsgBox s
End Sub

Function myTimer() As Currency
QueryPerformanceCounter myTimer
End Function

Function myElapsedTime(dt As Currency) As Double
If freq = 0 Then QueryPerformanceFrequency freq: df = freq
myElapsedTime = dt / df
End Function

On Apr 27, 10:11*am, joeu2004 wrote:
Instead, I resorted to doit3: *reading a range into one
variant, looping to copy that variant into a variant
array, then writing the entire second variant array to
a range in one statement.

As you should see, the performance of doit3 is significantly
better than doit2 -- about 1.7x on my computer (YMMV).

There is an important concept here to keep in mind -- one that might
be difficult to understand if you are not familiar with computer
science.

Every time you use Range, Evaluate etc, you are communicating between
two processes. And that communication delay is very costly in time.
It is like the difference between watching a reporter broadcast from a
local site v. in Iraq (and we are viewing in the US). Ever notice how
the video usually lags behind the audio or vice versa?

So it is usually very much better to access Excel once and iterate in
VBA than to iterate between VBA and Excel, even if that requires
additional work in VBA.

-----

Can you tell me what the last parameter is in the IF expression in
Sim_bond_calc!I26?

If is a #REF in my XL2003 conversion (that someone provided to me),
undoubtedly because it originally referenced a row beyond 65536 or a
deleted row.

(I would expect it to reference M25, based on the paradigm in I27
etc. But M25 is text.)

Also, what should be in Sim_bond_calc!C24:M24?

There are all constant zero in my XL2003 conversion. I suspect that
is not right.

I think those are the only abberations that prevent me from running
the simulation -- although I also cannot do anything with the pivot
table :-(.

PS.... It would be better if Sim_bond_calc!C27 were

=EDATE($C$26,ROWS($C$27:C27)*3)

which you can copy down. With your current formulation, see what
happens when C26 is 8/31/2014.

On 27 apr, 20:30, joeu2004 wrote:
On Apr 27, 10:11*am, joeu2004 wrote:

Instead, I resorted to doit3: *reading a range into one
variant, looping to copy that variant into a variant
array, then writing the entire second variantarrayto
a range in one statement.

As you should see, the performance of doit3 is significantly
better than doit2 -- about 1.7x on my computer (YMMV).

There is an important concept here to keep in mind -- one that might
be difficult to understand if you are not familiar with computer
science.

Every time you use Range, Evaluate etc, you are communicating between
two processes. *And that communication delay is very costly in time.
It is like the difference between watching a reporter broadcast from a
local site v. in Iraq (and we are viewing in the US). *Ever notice how
the video usually lags behind the audio or vice versa?

So it is usually very much better to access Excel once and iterate in
VBA than to iterate between VBA and Excel, even if that requires
additional work in VBA.

-----

Can you tell me what the last parameter is in the IF expression in
Sim_bond_calc!I26?

If is a #REF in my XL2003 conversion (that someone provided to me),
undoubtedly because it originally referenced a row beyond 65536 or a
deleted row.

(I would expect it to reference M25, based on the paradigm in I27
etc. *But M25 is text.)

Also, what should be in Sim_bond_calc!C24:M24?

There are all constant zero in my XL2003 conversion. *I suspect that
is not right.

I think those are the only abberations that prevent me from running
the simulation -- although I also cannot do anything with the pivot
table :-(.

PS.... *It would be better if Sim_bond_calc!C27 were

=EDATE($C$26,ROWS($C$27:C27)*3)

which you can copy down. *With your current formulation, see what
happens when C26 is 8/31/2014.

Thanks again, you are a lot faster than I am... I will try use the
logic from doit3() later on but first I want to complete embed the
array logic in the cash_flow project macro.
Regarding your remarks:
1) #Ref in Sim_Bond_Cal!I26 indeed refers to M25 (which is indeed
text / shouldn't be an issue since the event cannot occur)
2) Sim_bond_calc!C24:M24: these are indeed zero's / I initially
refered to these cell because I thought the offset should be
flexibile. I guess it would be better to a put the 0 columns offset in
the formula's
3) Sim_bond_calc!C27. You are absolutely right, thanks for the
suggestion!!

I have stored an excel 2003 version of the file on the following
website http://www.filedropper.com/exampleibnd2excel2003 . I have
capped the last row of all named ranges to 65000 so I hope the pivot
now works.

On Apr 27, 10:45*pm, Paul S wrote:
I will try use the logic from doit3() later on but
first I want to complete embed the array logic in
the cash_flow project macro.

There is some confusion. You asked about the logic in my version of
sim_mortgage_production, or so I thought. doit3 simply demonstrates
that same logic; it is nothing new to try later.

It is to be compared with doit2, which demonstrates my interpretation
of the alternative I thought you were asking about ("one more
question").

Paul wrote
1) #Ref in Sim_Bond_Cal!I26 indeed refers to M25 (which
is indeed text / shouldn't be an issue since the event
cannot occur)

Yeah, I finally figured that out myself.

Paul wrote:
2) Sim_bond_calc!C24:M24: these are indeed zero's
I initially refered to these cell because I thought
the offset should be flexibile. I guess it would be
better to a put the 0 columns offset in the formula's

Yeah, I figured that out myself, too. It would be better to eschew
all of the OFFSET logic if you do not intend to have multiple parallel
variations of the data in C10:C22.

Paul wrote:
I have stored an excel 2003 version of the file on the
following website http://www.filedropper.com/exampleibnd2excel2003.

Thanks. I'll have a look-see late tomorrow. I am curious to see how
all this works, to see what the performance is like, and to see if
there are opportunities for major improvements.

On Apr 27, 10:45*pm, Paul S wrote:
I have stored an excel 2003 version of the file on the
following website
http://www.filedropper.com/exampleibnd2excel2003.

FYI, I cannot open that file successfully. First, it was saved as an
XL2007 xlsm file, not as an "XL2003" xls file. Second, the Office
conversion utility or XL2003 stumbles over a fatal internal error.

No matter: I do not need to look at the entire Excel file.

If you want me to look at the macros, you can simply copy-and-paste
the text to Notepad and upload the txt file to a file-sharing website.

Paul wrote:
I have capped the last row of all named ranges to 65000

And yet I still get an error to the effect of: "This workbook
contains data in cells outside of the row and column limit of the
selected file format. Data beyond 256 (IV) columns by 65,536 rows will
not be saved. Formula references to data in this region will return a
#REF! error."

Go figure!

Paul wrote:
I hope the pivot now works.

It is my (weak) understanding that no XL2007 pivot table is compatible
with XL2003. At least, that is the sense that I get from the
"compatibility report" in the previous version. It states:

"A PivotTable style is applied to a PivotTable in this workbook.
PivotTable style formatting cannot be displayed in earlier versions of
Excel."

"A PivotTable in this workbook will not work in versions prior to
Excel 2007. Only PivotTables that are created in Compatibility Mode
will work in earlier versions of Excel."