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

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.

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