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

--
Regards,
Tom Ogilvy

Jag Man wrote in message
...
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

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



"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

Jag Man wrote in message
...
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

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...31&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.

--
Regards,
Tm Ogilvy


Jag Man wrote in message
...
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



"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

Jag Man wrote in message
...
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

You passing a point to the array.

should say:

You are passing a pointer to the array.

--
Regards,
Tom Ogilvy

Tom Ogilvy wrote in message
...
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...31&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.

--
Regards,
Tm Ogilvy


Jag Man wrote in message
...
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



"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

Jag Man wrote in message
...
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

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


"Tom Ogilvy" wrote in message
...
You passing a point to the array.

should say:

You are passing a pointer to the array.

--
Regards,
Tom Ogilvy

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