Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » VBA Arrays
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
VBA Arrays
Resolved · Urgent Priority · Version 2007
Reply Tue 20 Sep 2011, 16:45Delegate Victor said...
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
For upcoming training course dates see: Pricing & availability
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
Reply Mon 26 Sep 2011, 11:54Delegate Victor said...
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
Reply Tue 25 Oct 2011, 15:38Delegate Victor said...
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
Tue 4 Oct 2011: Automatically marked as resolved.
|