View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.programming
Jag Man Jag Man is offline
external usenet poster
 
Posts: 38
Default Transferring ranges to/from arrays

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