Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to pass arrays of integers or doubles to VBA function in Excel
I have written a function in Excel's Visual Basic editor. It accepts two
arrays, one of Integer's and one of Double's. The function seems to compile just fine and other functions are working well for me, so I semi know what I am doing. In the spreadsheet, I would like to select a range of cells and pass them as the integer array. I would like to do the same for another contiguous range of cells to serve as the Double array. I tried simply doing just that but got errors in the spreadsheet. How do I do this? Is there some function in the spreadsheet that converts a range of cells into an array of numerical values? Or do I need to adapt the function to work in the spreadsheet's way of doing things and make it receive cell references from which it extracts the numerical values? TIA, BE |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to pass arrays of integers or doubles to VBA function in Excel
Hi
Range.Value is a variant array ie. Dim myValues as Variant myValues = myRange.Value You can access it like an array msgbox myValues(3,1) You can get its dimensions if you need to loop through the values myrows = UBound(myValues,1) 'number of rows mycolumns = UBound(myValues,2) 'number of columns For i = 1 to myrows For j = 1 to mycolumns msgbox myValues(i, j) next j next i Your function should now accept two such variants myfunction(myValaues1 as Variant, myValues2 as Variant) There is a proviso: the value of a single cell is NOT a 1 by 1 array, and you will have to handle that case if it arises. regards Paul On Mar 8, 7:23 pm, Bull Earwig <Bull wrote: I have written a function in Excel's Visual Basic editor. It accepts two arrays, one of Integer's and one of Double's. The function seems to compile just fine and other functions are working well for me, so I semi know what I am doing. In the spreadsheet, I would like to select a range of cells and pass them as the integer array. I would like to do the same for another contiguous range of cells to serve as the Double array. I tried simply doing just that but got errors in the spreadsheet. How do I do this? Is there some function in the spreadsheet that converts a range of cells into an array of numerical values? Or do I need to adapt the function to work in the spreadsheet's way of doing things and make it receive cell references from which it extracts the numerical values? TIA, BE |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to pass arrays of integers or doubles to VBA function in Excel
A couple of things to note. You need to leave the variables as type variant
but that should not be a big deal. Also ranges give 2D arrays, but if you transpose it you get a 1D array... so something like this... Sub test() Dim rng As Range Dim ary As Variant Set rng = Selection ary = Application.Transpose(rng.Value) Call test2(ary) End Sub Sub test2(ByRef ary As Variant) Dim i As Integer For i = LBound(ary) To UBound(ary) MsgBox ary(i) Next i End Sub -- HTH... Jim Thomlinson "Bull Earwig" wrote: I have written a function in Excel's Visual Basic editor. It accepts two arrays, one of Integer's and one of Double's. The function seems to compile just fine and other functions are working well for me, so I semi know what I am doing. In the spreadsheet, I would like to select a range of cells and pass them as the integer array. I would like to do the same for another contiguous range of cells to serve as the Double array. I tried simply doing just that but got errors in the spreadsheet. How do I do this? Is there some function in the spreadsheet that converts a range of cells into an array of numerical values? Or do I need to adapt the function to work in the spreadsheet's way of doing things and make it receive cell references from which it extracts the numerical values? TIA, BE |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to pass arrays of integers or doubles to VBA function in Excel
I think Jim was rushing his answer:
Also ranges give 2D arrays, but if you transpose it you get a 1D array... If your range is multiple rows with a single column in width, then this is true. If it is multiple columns and multiple rows, it is isn't If it is multiple columns and a single row, it isn't true To account for the other situation that can be converted, you could use this to illustrate. Sub testRevised() Dim rng As Range Dim ary As Variant Set rng = Selection If rng.Count = 1 Then MsgBox "Single cell" Exit Sub End If rw = rng.Rows.Count: col = rng.Columns.Count If rw 1 Then If col = 1 Then ary = Application.Transpose(rng.Value) Call test2(ary) Else MsgBox "Multiple rows, multiple columns" End If Else If rw = 1 Then ary = Application.Transpose( _ Application.Transpose(rng.Value)) Call test2(ary) End If End If End Sub ' no change Sub test2(ByRef ary As Variant) Dim i As Integer For i = LBound(ary) To UBound(ary) MsgBox ary(i) Next i End Sub So if multiple columns and a single row, you need to do the tranpose twice. -- regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... A couple of things to note. You need to leave the variables as type variant but that should not be a big deal. Also ranges give 2D arrays, but if you transpose it you get a 1D array... so something like this... Sub test() Dim rng As Range Dim ary As Variant Set rng = Selection ary = Application.Transpose(rng.Value) Call test2(ary) End Sub Sub test2(ByRef ary As Variant) Dim i As Integer For i = LBound(ary) To UBound(ary) MsgBox ary(i) Next i End Sub -- HTH... Jim Thomlinson "Bull Earwig" wrote: I have written a function in Excel's Visual Basic editor. It accepts two arrays, one of Integer's and one of Double's. The function seems to compile just fine and other functions are working well for me, so I semi know what I am doing. In the spreadsheet, I would like to select a range of cells and pass them as the integer array. I would like to do the same for another contiguous range of cells to serve as the Double array. I tried simply doing just that but got errors in the spreadsheet. How do I do this? Is there some function in the spreadsheet that converts a range of cells into an array of numerical values? Or do I need to adapt the function to work in the spreadsheet's way of doing things and make it receive cell references from which it extracts the numerical values? TIA, BE |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to pass arrays of integers or doubles to VBA function in E
Absolutely correct... When I had originally answered I thought that the OP
had indicated the ranges were individual columns... Obviously not the case. I should have been more detailed no matter what though. Thanks Tom! -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: I think Jim was rushing his answer: Also ranges give 2D arrays, but if you transpose it you get a 1D array... If your range is multiple rows with a single column in width, then this is true. If it is multiple columns and multiple rows, it is isn't If it is multiple columns and a single row, it isn't true To account for the other situation that can be converted, you could use this to illustrate. Sub testRevised() Dim rng As Range Dim ary As Variant Set rng = Selection If rng.Count = 1 Then MsgBox "Single cell" Exit Sub End If rw = rng.Rows.Count: col = rng.Columns.Count If rw 1 Then If col = 1 Then ary = Application.Transpose(rng.Value) Call test2(ary) Else MsgBox "Multiple rows, multiple columns" End If Else If rw = 1 Then ary = Application.Transpose( _ Application.Transpose(rng.Value)) Call test2(ary) End If End If End Sub ' no change Sub test2(ByRef ary As Variant) Dim i As Integer For i = LBound(ary) To UBound(ary) MsgBox ary(i) Next i End Sub So if multiple columns and a single row, you need to do the tranpose twice. -- regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... A couple of things to note. You need to leave the variables as type variant but that should not be a big deal. Also ranges give 2D arrays, but if you transpose it you get a 1D array... so something like this... Sub test() Dim rng As Range Dim ary As Variant Set rng = Selection ary = Application.Transpose(rng.Value) Call test2(ary) End Sub Sub test2(ByRef ary As Variant) Dim i As Integer For i = LBound(ary) To UBound(ary) MsgBox ary(i) Next i End Sub -- HTH... Jim Thomlinson "Bull Earwig" wrote: I have written a function in Excel's Visual Basic editor. It accepts two arrays, one of Integer's and one of Double's. The function seems to compile just fine and other functions are working well for me, so I semi know what I am doing. In the spreadsheet, I would like to select a range of cells and pass them as the integer array. I would like to do the same for another contiguous range of cells to serve as the Double array. I tried simply doing just that but got errors in the spreadsheet. How do I do this? Is there some function in the spreadsheet that converts a range of cells into an array of numerical values? Or do I need to adapt the function to work in the spreadsheet's way of doing things and make it receive cell references from which it extracts the numerical values? TIA, BE |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What function to select the last 3 smallest integers? | Excel Worksheet Functions | |||
What function to select the last 3 smallest integers? | Excel Discussion (Misc queries) | |||
VBA: How to pass arrays in Function Calls? | Excel Programming | |||
A list of Consecutive Integers, can I search for missing integers | Excel Worksheet Functions | |||
How do I pass a worksheets name to function? | Excel Programming |