Victor has attended:
Excel VBA Intro Intermediate course
VBA Arrays
Can you upload a group of worksheets (formulae and all) to an array e.g. having an array of 6 worksheets?
Thanks
RE: VBA Arrays
Hi Victor, thanks for your query. You can indeed upload worksheets into an array, like so:
Dim myarray(1 To 6) As Variant
Set myarray(1) = Sheets("January")
Set myarray(2) = Sheets("February")
...and so on.
Hope this helps,
Anthony
RE: VBA Arrays
Thanks Anthony,
A couple of follow up questions - will this preserve the formulae in the sheets?
e.g. if sheet X were in an array and cell A1=A2+A3, and the macro were to change the value of A2 in sheet X the array, would A1 also update?
How do I refer to cell A3 for example, in the array sheet, is it akin to using a named sheet e.g. NameOfSheetInArray.Range...
Hope that's a clear question! Victor
RE: VBA Arrays
Hi Victor, thanks for your query. The answer is yes. Here's an illustration. Create a worksheet "MySheet", A1=1, A2=2, A2=3, sum those values in a4 then run the following subroutine:
Sub test()
Dim myarray(1) As Worksheet
Dim sheetobj As Worksheet
Set myarray(1) = Sheets("MySheet")
MsgBox myarray(1).Cells(4, 1) 'returns 6 from the array
myarray(1).Cells(1, 1) = 5 'writes a new value to the array, and to the sheet
End Sub
Hope this helps,
Anthony
RE: VBA Arrays
Hi Anthony,
Thanks for the above. A follow on question:
I have written some code based on the above and some other pointers I've found online:
/***
sub NewArrayTest()
Dim Worksheet As Variant
Dim wCohortEngine() As Worksheet
Dim wCohortResults(0) As Worksheet
Dim intTestArrayNumber As Integer
intTestArrayNumber = shtcohortresults.Range("H7").Value - shtcohortresults.Range("H6").Value
Dim intCurrentAge As Integer
intCurrentAge = 0
Set wCohortResults(0) = shtcohortresults
ReDim wCohortEngine(intTestArrayNumber)
For Each Worksheet In wCohortEngine
Set wCohortEngine(intCurrentAge) = shtcohortmodel
wCohortEngine(intCurrentAge).Range("c125").Value = 1 + intCurrentAge
'Debug.Print wCohortEngine.Range("c125").Value
wCohortResults(0).Range("d33:d40").Offset(0, intCurrentAge).Value = _
wCohortEngine(intCurrentAge).Range("d327:d334").Value
wCohortResults(0).Range("d49:d56").Offset(0, intCurrentAge).Value = _
wCohortEngine(intCurrentAge).Range("d335:d342").Value
'Debug.Print wCohortEngine.Range("d335").Value
'Debug.Print shtcohortresults.Range("d49").Value
intCurrentAge = intCurrentAge + 1
Next Worksheet
End Sub
**/
Currently, with testarraynumber=100 it takes approximately 15s to complete. I need to make this quicker as the next step for the analysis is to perform a monte carlo simulation based on the aggregated result of the code above.
My guess is that getting the worksheets in the array to update simultaneously instead of within a loop would be the quickest but I have no clue how to do this!
Anything you can suggest that may help would be awesome.
Thanks - Victor
