Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA arrays
I'm trying to get my head around lists (arrays?) in VBA. Lets say I want to create an array of random numbers whose length is defined by the user. Is this the right idea? 1 - It would ask the user how long the array should be. 2 - Then starting with an empty array, it would run through a for loop as many times as defined by the user, each time appending a new random number to the end. I can't seem to figure out the right way to use arrays in VBA. Is array(a, b, c) the best way to create them? How then do you add another number to it? Or access the nth value in the array? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA arrays
A static array is an array that is sized in the Dim statement that declares
the array. E.g., Dim StaticArray(1 To 10) As Long You cannot change the size or data type of a static array. When you Erase a static array, no memory is freed. Erase simple set all the elements to their default value (0, vbNullString, Empty, or Nothing, depending on the data type of the array). A dynamic array is an array that is not sized in the Dim statement. Instead, it is sized with the ReDim statement. E.g., Dim DynamicArray() As Long ReDim DynamicArray(1 To 10) ----------------------------------------------------------------------- This is an extract from Chip Person's article on VBA Arrays. Pl. visit http://www.cpearson.com/excel/VBAArrays.htm for the complete excellent article.. "Matt" wrote: While there you may also like to look at Arrays Formulas.... I'm trying to get my head around lists (arrays?) in VBA. Lets say I want to create an array of random numbers whose length is defined by the user. Is this the right idea? 1 - It would ask the user how long the array should be. 2 - Then starting with an empty array, it would run through a for loop as many times as defined by the user, each time appending a new random number to the end. I can't seem to figure out the right way to use arrays in VBA. Is array(a, b, c) the best way to create them? How then do you add another number to it? Or access the nth value in the array? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA arrays
Option Explicit
Sub testme() Dim myArr() As Double Dim HowMany As Long Dim iCtr As Long HowMany = CLng(Application.InputBox(Prompt:="How many?", Type:=1)) 'some minor validity If HowMany < 1 _ Or HowMany 100 Then MsgBox "that won't work!" Exit Sub End If ReDim myArr(1 To HowMany) Randomize 'shuffle For iCtr = LBound(myArr) To UBound(myArr) myArr(iCtr) = Rnd Next iCtr End Sub You could add a number to any element in the array: myArr(77) = myArr(77) + 5.32421 (assuming that you have 77 elements) You may want to visit J.E. McGimpsey's site and grab a copy of his =RandInt() function. http://www.mcgimpsey.com/excel/udfs/randint.html Matt wrote: I'm trying to get my head around lists (arrays?) in VBA. Lets say I want to create an array of random numbers whose length is defined by the user. Is this the right idea? 1 - It would ask the user how long the array should be. 2 - Then starting with an empty array, it would run through a for loop as many times as defined by the user, each time appending a new random number to the end. I can't seem to figure out the right way to use arrays in VBA. Is array(a, b, c) the best way to create them? How then do you add another number to it? Or access the nth value in the array? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA arrays
A quick way to load an array is from a range on a worksheet.
Dim ary Range("A1:A10").Formula = "=RAND()" ary = Application.Transpose(Range("A1:A10")) Range("A1:A10").ClearContents -- __________________________________ HTH Bob "Matt" wrote in message ... I'm trying to get my head around lists (arrays?) in VBA. Lets say I want to create an array of random numbers whose length is defined by the user. Is this the right idea? 1 - It would ask the user how long the array should be. 2 - Then starting with an empty array, it would run through a for loop as many times as defined by the user, each time appending a new random number to the end. I can't seem to figure out the right way to use arrays in VBA. Is array(a, b, c) the best way to create them? How then do you add another number to it? Or access the nth value in the array? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arrays | Excel Worksheet Functions | |||
Regarding Arrays | Excel Discussion (Misc queries) | |||
Regarding the Arrays | Excel Discussion (Misc queries) | |||
Arrays | Excel Discussion (Misc queries) | |||
Need help with arrays (I think) | Excel Worksheet Functions |