• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array using VBA

nagovind

Member
Dear All


For example i have some values say 1 to 1000

Is it possible using ARRAY function to store the values in array using a FOR loop by VBA so value inside the array will be 1, 2 , 3 ....1000


after For loop terminates i want to recall the value in array position say 58


so array (58) i have to use...i.e without storing the value in excel sheet....


In other words Before Scope of Variable ends i will use the value


I believe scope of a variable is still alive for a whole program during running (from Sub to end Sub)


Is it possible ?
 

Hui

Excel Ninja
Staff member
[pre]
Code:
Sub MyArray()
' This will set MyArray to 1000 items and
' fill them with No's 1 to 1000

Dim i As Integer
Dim MyArray(1000) As Variant

For i = 1 To 1000
MyArray(i) = i
Next

MsgBox MyArray(58) 'Show record 58

End Sub
[/pre]
 

Hui

Excel Ninja
Staff member
[pre]
Code:
Sub MyArray()
'This will import A1:A1000 to MyArray

Dim MyArray As Variant

MyArray = Worksheets("Sheet1").Range("A1:A1000").Value

MsgBox MyArray(58, 1) 'Show  A58

End Sub
[/pre]
 

Hui

Excel Ninja
Staff member
[pre]
Code:
Sub MyArray()
'This will import A1:A1000 to MyArray,
'process it and
'Write it back to somewhere else

Dim MyArray As Variant

MyArray = Worksheets("Sheet1").Range("A1:A1000").Value

MsgBox MyArray(58, 1) 'Show  A58

For i = 1 To 1000
MyArray(i, 1) = MyArray(i, 1) ^ 2 'Square each entry
Next

Worksheets("Sheet1").Range("B1:B1000").Value = MyArray
'Write MyArray back to B1:B1000

End Sub
[/pre]
 

nagovind

Member
Thanks Hui


Is it possible to go one more step


Sub MyArray()

' This will set MyArray to 1000 items and

' fill them with No's 1 to 1000


Dim i As Integer

Dim MyArray(1000) As Variant


For i = 1 To 1000

MyArray(i) = i

Next


Worksheets("Sheet1").Range("B1:B1000").Value = MyArray


'this will save my time at a stretch i will write all value of array into cells with one line command this is not working ?


End Sub
 

Hui

Excel Ninja
Staff member
[pre]
Code:
Sub MyArray()

Dim i As Integer
Dim MyArray(1 To 1000, 1 to 1) As Variant

For i = 1 To 1000
MyArray(i, 1) = i
Next

Worksheets("Sheet1").Range("B1:B1000").Value = MyArray
End Sub
[/pre]
 

nagovind

Member
Thanks Hui

Its working

In general using this array function reduced the RUNNING time of the progream where i have lengthy of nummber about 50000...

thanks
 

Hui

Excel Ninja
Staff member
Whenever you can reduce the number of direct Read/Writes from VBA to a Worksheet you should


In your case writing to each cell individually is 1000 Excel write actions

Writing to the Range in 1 pass using the array will only take marginally longer than 1 write and so you save nearly 999 writes and so yes it gives you a huge performance boost.
 

nagovind

Member
Thanks Hui

It is amazing and a considerable PERFORMANCE BOOST

it saved more time

Now program is working in seconds instead of Minutes as earlier !!!
 
Top