Reading Arrays Into Visual Basic

[djt76010]

If I have an Excel spreadsheet with named range "Array_1" referring to range A1:A20, what is the most efficient way to get the data from "Array_1" into an array in Visual Basic if I am creating a user defined function that has the text "Array_1" passed into it as a variable?

 

[JoeMo]

In your sub routine that will call your UDF, and with the sheet containing the named range Array_1 active:

Dim vArr as variant
vArr = Range("Array_1").Value
'rest of your code, including a call to the UDF that will pass vArr as the 'argument


vArr is a 2-D array with base 1: 20 x 1 (rows x columns). So, for example, vArr(2,1) =Range("A2").Value (the 2nd cell in Array_1 range.

In your UDF dim the array variable as variant:
Function myFunction(Arr as variant) ...

 

[Ruddles]

If I understand you correctly, just a Dim and a Transpose will do the job. Assuming the name of the range comes in as a string called aName, place this at the beginning of the UDF:-

  Dim aStore As Variant
 
  aStore = Application.Transpose([COLOR=red]ThisWorkbook.Sheets("Sheet1")[/COLOR].Range(aName))

Change the bit in red to point to the correct worksheet.

If you have problems with this, show me your existing UDF code and I'll put it in the right place.

 

[djt76010]

Thanks JoeMo,

this seems to have done the trick.

In your sub routine that will call your UDF, and with the sheet containing the named range Array_1 active:

Dim vArr as variant
vArr = Range("Array_1").Value
'rest of your code, including a call to the UDF that will pass vArr as the 'argument


vArr is a 2-D array with base 1: 20 x 1 (rows x columns). So, for example, vArr(2,1) =Range("A2").Value (the 2nd cell in Array_1 range.

In your UDF dim the array variable as variant:
Function myFunction(Arr as variant) ...

 

[djt76010]

Thanks Ruddles,

I got that to work too. Anyone out there know if there are any differences between these two methods from an efficiency point of view? I am in the process of trying to make some code faster and this function is the building block that others are built off of.

 

[djt76010]

How can I change the array index? If I try to declare the variable as an array I get a compile error "Can't assign to an array". For my purposes, ideally the first item in the array would be 0 instead of 1. I realize I could subtract 1 from the index in the code, but for checking purposes the cleaner code would make things much easier.

 

[Ruddles]

Anyone out there know if there are any differences between these two methods from an efficiency point of view? I am in the process of trying to make some code faster and this function is the building block that others are built off of.

I wouldn't rely on someone else's opinion if it's that critical. Write a short piece of code like:-

dim dtstart as date
dim iptr as long
dtstart=now()
for iptr=1 to 1000000
  call [I]subroutinename[/I]
next iptr
msgbox format(now()-dtstart,"hh:nn:ss")

where subroutinename is a procedure which loads the array.

Try both methods and see which one is the fastest.

 

[Ruddles]

How can I change the array index?

I'm not sure you can but I'm probably not the best person to ask.

 

[meldoc]

Hello

This will answer some of your questions.

http://www.anthony-vba.kefra.com/vba/vbabasic3.htm

 

[pgc01]

How can I change the array index? If I try to declare the variable as an array I get a compile error "Can't assign to an array". For my purposes, ideally the first item in the array would be 0 instead of 1. I realize I could subtract 1 from the index in the code, but for checking purposes the cleaner code would make things much easier.

Hi

Test this:

Dim arr As Variant
 
arr = Application.Transpose(Range("A1:A3"))
ReDim Preserve arr(0 To 2)