Transferring ranges to/from arrays

  • Thread starter Thread starter Jag Man
  • Start date Start date
J

Jag Man

What is the most efficient way to load data from a sheet range into an array
so I can
pass the data to a DLL function? And, to transfer the results back into
another sheet range?

That is, I want to do something like this

Sub mySub

inargs(0) = Range("x").value
inargs(1) = Range("y").value
inargs(2) = Range("z").value
ec = theDllFunction(inargs(0), outargs(0)

Range("a").Value = outargs(0)
Range("b").Value = outargs(1)
Range("c").Value = outargs(2)

End

The above works, but if the cells named x, y, z, and those named a, b, c,
are in a contiguous ranges it seems there must be
a more efficient way to do this.


TIA

Ed
 
Dim inargs as Variant
inargs = Range("x").Resize(3,1).Value

' inargs is a 1 to 3, 1 to 1 array (two dimensions)

Range("x").Resize(3,1).Value = outargs
 
Thanks, Tom.

What is the theory behind going to a 2-dim array here? Is it
because x, y & z are names for consecutive cells in a column?

Also, how would I pass the array to my function, which is written in C++?
With 1-dim array I use:

Private Declare Function mixerSM Lib "hvacTKDLL.dll" _
(ByRef inArgs As Double, ByRef outArgs As Double) As Long

Sub mixerDriver()
Dim inArgs(5) As Double, outArgs(6) As Double
Dim ec
inArgs(0) = Worksheets("mixer").Range("wAirEnt1").Value ' Test value
= 0.01
inArgs(1) = Worksheets("mixer").Range("wAirEnt2").Value ' Test value
= 0.005
...
ec = mixerSM(inArgs(0), outArgs(0))
If ec = 0 Then
Worksheets("mixer").Range("mAirEnt2").Value = outArgs(0)
Worksheets("mixer").Range("mAirEnt1").Value = outArgs(1)
....
End If
End Sub

But if inArgs is 2-dimensional, presumably I would do
ec = mixerSM(inArgs(0,0), outArgs(0,0))

But the compiler doesn't like it.

Sorry to be so dense....


Ed
 
You passing a point to the array. Here is an article on passing and using
safearrays - the information on the array structure is contained in the
array

http://support.microsoft.com/default.aspx?scid=kb;en-us;207931&Product=vcc
HOWTO: Pass Arrays Between Visual Basic and C

If you have already made provisions for working with these, your function
should be picking up that information already.


When you pick up a range from a worksheet, it creates a two dimensional
array even if it is just a single column or single row.

You can convert a column array to one dimesion if you do

Dim varr as variant
varr = application.Transpose(Range("A1:A10"))

this will produce a one dimensional array of 10 elements

In most verions of excel, use of Transpose is limited to 5461 elements.
After that you will get a type mismatch error.
 
You passing a point to the array.

should say:

You are passing a pointer to the array.
 
Yes, that's what I would assume from C/C++. But, if I hand off inArgs(0,0)
isn't that the same thing?
Yet, VBA doesn't like it. Hmm.

Ed
 
One other thought

an array picked up from the worksheet like that is 1 based. so the first
element is 1,1

myarray(1 to # rows, 1 to # columns)

That has little relevance in your DLL, but at in the argument list, it
might.

--
Regards,
Tom Ogilvy

Jag Man said:
Yes, that's what I would assume from C/C++. But, if I hand off inArgs(0,0)
isn't that the same thing?
Yet, VBA doesn't like it. Hmm.

Ed
Click to expand...
 
Tom,
I think I have this figured out, but what I've come to isn't elegant. The
upshot is that (a) Only Variants can
be used to move data to/from ranges, (b) the functions in my DLL want ByRef
Doubles, and (c) Since one
cannot assign to arrays, the array of doubles has to be assigned on a
one-at-a-time basis.

Since I want to use the same DLL functions from other languages as well as
VBA, I don't want to go to SafeArray.

Let me know if you can disput my findings, as I could be missing something
yet...

Here is what worked:

Private Declare Function enthalpySM Lib "hvacTKDLL.dll" _
(ByRef inArgs As Double, ByRef outArgs As Double) As Long
'
' Trying to set up array args from sheet columns
'
Sub ArrayArgTest()
Dim inVars As Variant
Dim outVars As Variant
Dim inArgs(2) As Double, outArgs(1) As Double
Dim ec As Long
inVars = Range("TDb").Resize(2, 1).Value ' Get a column from sheet

'inArgs = inVars ' Won't work, as one cannot assign to array
For i = 0 To 1
inArgs(i) = inVars(i + 1, 1)
Next i

' ec = enthalpySM(inVars(1, 1), outVars(1, 1)) ' Wont work. Gives a Type
mismatch error
ec = enthalpySM(inArgs(0), outArgs(0)) ' my args must be ByRef Doubles

outVars = outArgs ' Here I can do it the easy way
Range("h").Resize(1, 1).Value = outArgs ' Put a column into the sheet
End Sub


Ed


Tom Ogilvy said:
One other thought

an array picked up from the worksheet like that is 1 based. so the first
element is 1,1

myarray(1 to # rows, 1 to # columns)

That has little relevance in your DLL, but at in the argument list, it
might.
Click to expand...
[/QUOTE]
 
Jag Man wrote:
.. . .The
upshot is that (a) Only Variants can
be used to move data to/from ranges . . . .
Click to expand...

From yes, to no. The following works:

Sub testIt()
Dim arr(3) As Long
arr(1) = 1
arr(2) = 2
arr(3) = 3
Range("A1:C1").Value = arr
End Sub

Alan Beban
 
Alan,

Oops! Yes, you are right. As a matter of fact, I demonstrated that in
my example. But, can you contradict my conclusion that you can't do the
reverse?
E.g.,

inVars = Range("x").Resize(2, 1).Value
Dim inArgs(2) As Double

inArgs = inVars ' Won't work. Cannot assign to array

Thanks for your interest.


Ed
 
Jag said:
Alan,

Oops! Yes, you are right. As a matter of fact, I demonstrated that in
my example. But, can you contradict my conclusion that you can't do the
reverse?
Click to expand...

Nope (although the code below doesn't attempt to assign a range to the
array inArgs; inVars does not refer to a range, but is a Variant()
variable/array)

Alan Beban
 
I now believe it is impossible to avoid transferring values one-by-one both
for
setting up the input array and for returning the output to the sheet. I can
get the
inputs from a Range in one fell swoop, going into a Variant inVars. However,
since I need
these values in an array of doubles for my function call, and the compiler
won't allow
array assignment, I have to loop to load the inArgs array.

After the function call I can assign the outArgs array to a Variant
outVars, then
execute what one would THINK would insert the outVars into a range of cells,
but the result is not that at all; it merely replicates the first value into
those cells!
Looking at an example in John Green's book "Excel 2000 VBA" he uses
Dim outVars() As Variant for assigning into a range. However, when I do that
I am foiled again because the statement outVars = outArgs is no longer
legal---
the old "cannot assign arrays" strikes again.

Sorry for the rant, but I remember when BASIC used to be more intuitive....

Ed

Sub Driver()
Dim inVars As Variant
Dim outVars As Variant
Dim nInputs As Integer, nOutputs As Integer
nInputs = 6
nOutputs = 7
inVars = Range("$B$2").Resize(nInputs, 1).Value
Dim inArgs() As Double, outArgs() As Double
ReDim inArgs(nInputs - 1)
ReDim outArgs(nOutputs - 1)

'inArgs = inVars ' Cannot assign to array
' So must do it the hard way
For i = 0 To nInputs - 1
inArgs(i) = inVars(i + 1, 1)
Next i

ec = mixerSM(inArgs(0), outArgs(0))

outVars = outArgs ' This works
Range("$I$2").Resize(nOutputs, 1).Value = outVars ' This compiles,
but does not produce expected result

' It replicates the first outVars value in every cell!
End Sub
 
Alan,

When I execute your example below I get 0, 1, 2 in the specified range.
Curiously,
if I change the range to "A10:A12" I get 1, 1, 1 in the specified range!

What is going on here, I wonder?


Ed
 
In my test Module I have the Option Base 1 Statement effective, so the
first line of my code is equivalent to Dim arr(1 to 3); In your Module
you do not have the Option Base 1 Statement effective, so your code is
equivalent to Dim arr(0 to 3).

As to the second point, which also answers your post of 1:24 pm, the
array is one-dimensional and "horizontal"; therefore, to transfer it to
a vertical range you might use

Range("A10:A12").Value = Application.Transpose(arr)

Alan Beban
 
Back
Top