Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array function, two dimensions?? and worksheet arrays
Hi
example A Dim A As Variant A = Array(10,20,30) B = A(2) the above is straight from Excel help on array functions. If have used two dimension arrays like this befo example B dim stuff(20,20) as integer where stuff( var1, var2) gets me to where I want. Q1) Can you 'do' exampleA in two dimensions? Q2) the Excel text for example A says that 10,20,30 arglist are values. Can what's inside the parenthesis be variable names containing values? Q3) New topic: I can't seem to find an example of how to specify an array of cells for worksheet formulas (NOT a vba array) will you provide a brief example? Thanks much, -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array function, two dimensions?? and worksheet arrays
Here is an example from "real life". I did not take anything out of this to make it easier to understand, so you may need to study
it for some time. This is a 2-dimension array example and it will show you how to load up both dimensions with variables from the workbook. Sub GetTeamExtract() Dim MovingExtract(100, 16) As Variant Dim E As Long Dim Z As Long Sheets("Team Data Table").Activate Range("B7").Activate E = 0 Do Until ActiveCell = "" 'loop through the list of Teams and copy their data 'to their data table ActiveWorkbook.Names.Add Name:="Bookmark", RefersTo:=ActiveCell N = ActiveCell.Text 'set variable to = Team names as it searches for their data Range("G6:G286").Select 'go to the column in the table that holds the names Cells.Find(What:=N, After:=Range("G6"), LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate 'find the current Team in the table ActiveCell.Select ActiveCell.Offset(11, 66).Select 'select the 3 mo moving avg. for that Team For Z = 0 To 15 MovingExtract(E, Z) = Selection 'load up array with values for each person ActiveCell.Offset(0, 1).Select Next ActiveCell.Offset(0, -16).Select E = E + 1 Application.Goto Reference:="Bookmark" 'go back to the list of Teams on the data page ActiveCell.Offset(1, 0).Activate 'move down one cell Loop 'loop to the next Team in the list Sheets("Extract Averages").Activate Range("Bookmark2").Select ActiveCell.Offset(0, 1).Activate For i = 0 To E For Z = 0 To 15 ActiveCell.Offset(0, Z) = MovingExtract(i, Z) Next ActiveCell.Offset(1, 0).Activate 'move down one cell Next 'i End Sub HTH -- RMC,CPA "Neal Zimm" wrote in message ... Hi example A Dim A As Variant A = Array(10,20,30) B = A(2) the above is straight from Excel help on array functions. If have used two dimension arrays like this befo example B dim stuff(20,20) as integer where stuff( var1, var2) gets me to where I want. Q1) Can you 'do' exampleA in two dimensions? Q2) the Excel text for example A says that 10,20,30 arglist are values. Can what's inside the parenthesis be variable names containing values? Q3) New topic: I can't seem to find an example of how to specify an array of cells for worksheet formulas (NOT a vba array) will you provide a brief example? Thanks much, -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array function, two dimensions?? and worksheet arrays
Q1) Can you 'do' exampleA in two dimensions?
Yes, if you Dim the 2-D array as a variant. Only variants can hold arrays... even arrays of arrays. Q2) the Excel text for example A says that 10,20,30 arglist are values. Can what's inside the parenthesis be variable names containing values? No. Only values can be stored using the "Array" identifier. However, you can store objects that are "pointers" to values. For example, Dim arr(0 To 1) As Variant 'Variant array Dim i As Integer Dim j As Integer arr(0) = Array(Range("a1"), Range("b2"), Range("c3")) 'Range of cells, arr(1) = Array(Range("a2"), Range("b3"), Range("c4")) 'not their values For i = 0 To 1 For j = 0 To 2 Debug.Print arr(i)(j).Value 'This works! Boy, was I surprised! Next j Next i Q3) New topic: I can't seem to find an example of how to specify an array of cells for worksheet formulas (NOT a vba array) will you provide a brief example? Not quite sure what you mean here. Are you talking about array formulas in Excel? Lookup the FormulaArray property in VBA help. Note that you have to use R1C1 nomenclature in the formulae in this instance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array function, two dimensions?? and worksheet arrays
Q1) Can you 'do' exampleA in two dimensions?
No You can do an array of arrays to simulate two dimensions, but you can't create a two dimensional array in one command using the Array function. You can do Dim A as Variant A = Evaluate("{1,2,3;4,5,6;7,8,9;10,11,12}") will initialize a 4 x 3 array Q2) the Excel text for example A says that 10,20,30 arglist are values. Can what's inside the parenthesis be variable names containing values? Yes demo'd from the immediate window f = 3 j = ll h = 12 A = Array(f, j, h) ? a(2) 12 ? a(0) 3 Q3) New topic: I can't seem to find an example of how to specify an array of cells for worksheet formulas (NOT a vba array) will you provide a brief example? ActiveCell.Formula = "=Sum(A1:A10,C1:C10)" or set rng = Range("A1:A0,C1:C10") ActiveCell.Formula = "=Sum(" & rng.Address & ")" -- Regards, Tom Ogilvy "Neal Zimm" wrote in message ... Hi example A Dim A As Variant A = Array(10,20,30) B = A(2) the above is straight from Excel help on array functions. If have used two dimension arrays like this befo example B dim stuff(20,20) as integer where stuff( var1, var2) gets me to where I want. Q1) Can you 'do' exampleA in two dimensions? Q2) the Excel text for example A says that 10,20,30 arglist are values. Can what's inside the parenthesis be variable names containing values? Q3) New topic: I can't seem to find an example of how to specify an array of cells for worksheet formulas (NOT a vba array) will you provide a brief example? Thanks much, -- Neal Z |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array function, two dimensions?? and worksheet arrays
Thanks so much, I'll start to look at it.
-- Neal Z "R. Choate" wrote: Here is an example from "real life". I did not take anything out of this to make it easier to understand, so you may need to study it for some time. This is a 2-dimension array example and it will show you how to load up both dimensions with variables from the workbook. Sub GetTeamExtract() Dim MovingExtract(100, 16) As Variant Dim E As Long Dim Z As Long Sheets("Team Data Table").Activate Range("B7").Activate E = 0 Do Until ActiveCell = "" 'loop through the list of Teams and copy their data 'to their data table ActiveWorkbook.Names.Add Name:="Bookmark", RefersTo:=ActiveCell N = ActiveCell.Text 'set variable to = Team names as it searches for their data Range("G6:G286").Select 'go to the column in the table that holds the names Cells.Find(What:=N, After:=Range("G6"), LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate 'find the current Team in the table ActiveCell.Select ActiveCell.Offset(11, 66).Select 'select the 3 mo moving avg. for that Team For Z = 0 To 15 MovingExtract(E, Z) = Selection 'load up array with values for each person ActiveCell.Offset(0, 1).Select Next ActiveCell.Offset(0, -16).Select E = E + 1 Application.Goto Reference:="Bookmark" 'go back to the list of Teams on the data page ActiveCell.Offset(1, 0).Activate 'move down one cell Loop 'loop to the next Team in the list Sheets("Extract Averages").Activate Range("Bookmark2").Select ActiveCell.Offset(0, 1).Activate For i = 0 To E For Z = 0 To 15 ActiveCell.Offset(0, Z) = MovingExtract(i, Z) Next ActiveCell.Offset(1, 0).Activate 'move down one cell Next 'i End Sub HTH -- RMC,CPA "Neal Zimm" wrote in message ... Hi example A Dim A As Variant A = Array(10,20,30) B = A(2) the above is straight from Excel help on array functions. If have used two dimension arrays like this befo example B dim stuff(20,20) as integer where stuff( var1, var2) gets me to where I want. Q1) Can you 'do' exampleA in two dimensions? Q2) the Excel text for example A says that 10,20,30 arglist are values. Can what's inside the parenthesis be variable names containing values? Q3) New topic: I can't seem to find an example of how to specify an array of cells for worksheet formulas (NOT a vba array) will you provide a brief example? Thanks much, -- Neal Z |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array function, two dimensions?? and worksheet arrays
Thanks, it's what the 'doctor' ordered.
-- Neal Z "Eric White" wrote: Q1) Can you 'do' exampleA in two dimensions? Yes, if you Dim the 2-D array as a variant. Only variants can hold arrays... even arrays of arrays. Q2) the Excel text for example A says that 10,20,30 arglist are values. Can what's inside the parenthesis be variable names containing values? No. Only values can be stored using the "Array" identifier. However, you can store objects that are "pointers" to values. For example, Dim arr(0 To 1) As Variant 'Variant array Dim i As Integer Dim j As Integer arr(0) = Array(Range("a1"), Range("b2"), Range("c3")) 'Range of cells, arr(1) = Array(Range("a2"), Range("b3"), Range("c4")) 'not their values For i = 0 To 1 For j = 0 To 2 Debug.Print arr(i)(j).Value 'This works! Boy, was I surprised! Next j Next i Q3) New topic: I can't seem to find an example of how to specify an array of cells for worksheet formulas (NOT a vba array) will you provide a brief example? Not quite sure what you mean here. Are you talking about array formulas in Excel? Lookup the FormulaArray property in VBA help. Note that you have to use R1C1 nomenclature in the formulae in this instance. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array function, two dimensions?? and worksheet arrays
As always Tom,
Concise, to the point, clever and accurate. Thanks. How can I get in touch with you outside of this bulletin board? Snail mail or otherwise? Many Thanks, -- Neal Z "Tom Ogilvy" wrote: Q1) Can you 'do' exampleA in two dimensions? No You can do an array of arrays to simulate two dimensions, but you can't create a two dimensional array in one command using the Array function. You can do Dim A as Variant A = Evaluate("{1,2,3;4,5,6;7,8,9;10,11,12}") will initialize a 4 x 3 array Q2) the Excel text for example A says that 10,20,30 arglist are values. Can what's inside the parenthesis be variable names containing values? Yes demo'd from the immediate window f = 3 j = ll h = 12 A = Array(f, j, h) ? a(2) 12 ? a(0) 3 Q3) New topic: I can't seem to find an example of how to specify an array of cells for worksheet formulas (NOT a vba array) will you provide a brief example? ActiveCell.Formula = "=Sum(A1:A10,C1:C10)" or set rng = Range("A1:A0,C1:C10") ActiveCell.Formula = "=Sum(" & rng.Address & ")" -- Regards, Tom Ogilvy "Neal Zimm" wrote in message ... Hi example A Dim A As Variant A = Array(10,20,30) B = A(2) the above is straight from Excel help on array functions. If have used two dimension arrays like this befo example B dim stuff(20,20) as integer where stuff( var1, var2) gets me to where I want. Q1) Can you 'do' exampleA in two dimensions? Q2) the Excel text for example A says that 10,20,30 arglist are values. Can what's inside the parenthesis be variable names containing values? Q3) New topic: I can't seem to find an example of how to specify an array of cells for worksheet formulas (NOT a vba array) will you provide a brief example? Thanks much, -- Neal Z |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array function, two dimensions?? and worksheet arrays
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT function for two arrays. Array 1 contains text | Excel Worksheet Functions | |||
SUMPRODUCT where arrays have different dimensions | Excel Discussion (Misc queries) | |||
Array function, two dimensions?? | Excel Programming | |||
Checking existence of array dimensions | Excel Programming | |||
Checking Number of Dimensions In Array | Excel Programming |