|
Welcome to the VBA
page !
To go back to the main page, please click on "Home" above.
You are now under the VBA page.
General Tips :
- Always use "Option Explicit" and either "Option Base 1" or "Option
Base 0". I would tend to prefer the Base 1 since it is more convenient
when you get values from a spreadsheet since Excel starts the arrays at
1. However be careful when translating code to/from C++.
To continue...
Typical exemples :
- Get values from a spreadsheet to an array : never do it cell by cell since it is very slow. Here is how to do it :
Option Explicit
Option Base 1
Function copyFromRangeToArray(myRange As Range) As Variant()
Dim myArray() As Variant
Dim k As Integer
ReDim myArray(1 To myRange.Rows.Count) As Variant
For k = 1 To UBound(myArray)
myArray(k) = myRange.Cells(k, 1)
Next k
copyFromRangeToArray = myArray
End Function
Sub test()
Dim newArray() As Variant
Dim myRange As Range
Set myRange = Range("E11:E16")
newArray = copyFromRangeToArray(myRange)
End Sub
This is not the fastest way but it gives a good idea of the kind of function that can be used...
|
