Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
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 wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
Jag Man wrote:
.. . .The upshot is that (a) Only Variants can be used to move data to/from ranges . . . . 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
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 Man wrote: . . .The upshot is that (a) Only Variants can be used to move data to/from ranges . . . . 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 Beban" wrote in message ... Jag Man wrote: . . .The upshot is that (a) Only Variants can be used to move data to/from ranges . . . . 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
Jag Man wrote:
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? 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 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 Man wrote: . . .The upshot is that (a) Only Variants can be used to move data to/from ranges . . . . 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 Beban" wrote in message ... Jag Man wrote: . . .The upshot is that (a) Only Variants can be used to move data to/from ranges . . . . 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
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 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
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 Jag Man wrote: 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 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transferring ranges to/from arrays
Alan,
Yea! Application.Transpose(outArgs) does the trick. Thanks! Ed "Alan Beban" wrote in message ... 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 Jag Man wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas in arrays and named ranges | Excel Discussion (Misc queries) | |||
Ranges and Arrays in Excel VBA | Excel Worksheet Functions | |||
comparing ranges/arrays | Excel Worksheet Functions | |||
Comparing two arrays/ranges | Excel Programming |