Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Array Function...What's wrong?
I am just trying to create an array function that returns an array containing
the values in a source Range. It shouldn't be that difficult, right? My source is below. I am running into the problem on this line: "Result(i, j) = Source(i, j).Value". The Result element is Empty after the assignment, even though Source(i,j).Value is not Empty. Help!? If you know why this assignment isn't working or if you know a better way to do this, please let me know. Function Range2Array(Source As Range) As Variant Dim rowCount, colCount As Integer rowCount = Source.Rows.count colCount = Source.Columns.count ' Ensure target is a range equal in size to source If Not (TypeOf Application.Caller Is Range _ And Application.Caller.Columns.count = colCount _ And Application.Caller.Rows.count = rowCount _ ) Then Exit Function End If ' Load result array Dim Result() As Variant ReDim Result(1 To rowCount, 1 To colCount) As Variant Dim i, j As Integer Result(1, 1) = "Test" For i = 1 To rowCount For j = 1 To colCount ' At this point Source(i,j).Value is not Empty Result(i, j) = Source(i, j).Value ' At this point the value of Result is Empty. Next j Next i ' Return result array Test = Result End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Array Function...What's wrong?
Hi Spatters71,
I tested out your code and the only thing I change is the 2nd line to the last "Test = Result" to "Range2Array = Result" The way I test your function is to start a new Excel file VB Editor Insert New Module Paste your code into it Change "Test" to "Range2Array" Return to Excel Enter arbitrary data into range A1:A25 hight light range C1:C25 type in "=Range2Array(A1:A25)" without quote Hold Ctrl + Shift and press Enter Data in range C1:C25 now has the same content as range A1:A25. I also use the step through and confirmed that After "Result(i, j) = Source(i, j).Value" Result is not Empty. Remember the way to get the result value from the function is through the name of the function itself. Hong Quach "Spatters71" wrote: I am just trying to create an array function that returns an array containing the values in a source Range. It shouldn't be that difficult, right? My source is below. I am running into the problem on this line: "Result(i, j) = Source(i, j).Value". The Result element is Empty after the assignment, even though Source(i,j).Value is not Empty. Help!? If you know why this assignment isn't working or if you know a better way to do this, please let me know. Function Range2Array(Source As Range) As Variant Dim rowCount, colCount As Integer rowCount = Source.Rows.count colCount = Source.Columns.count ' Ensure target is a range equal in size to source If Not (TypeOf Application.Caller Is Range _ And Application.Caller.Columns.count = colCount _ And Application.Caller.Rows.count = rowCount _ ) Then Exit Function End If ' Load result array Dim Result() As Variant ReDim Result(1 To rowCount, 1 To colCount) As Variant Dim i, j As Integer Result(1, 1) = "Test" For i = 1 To rowCount For j = 1 To colCount ' At this point Source(i,j).Value is not Empty Result(i, j) = Source(i, j).Value ' At this point the value of Result is Empty. Next j Next i ' Return result array Test = Result End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Array Function...What's wrong?
I cleaned up your function and also provided a shorter version...
'-- 'Used to call the functions - "2" and "22" Sub TestTheRangeFunctions() Dim x As Variant x = Range22Array(Selection) MsgBox x(3, 2) x = Range2Array(Selection) MsgBox x(3, 2) End Sub '----------- Function Range2Array(ByRef Source As Range) As Variant Dim rowCount As Long Dim colCount As Long Dim Result() As Variant Dim i As Long Dim j As Long If TypeName(Selection) < "Range" Then Exit Function rowCount = Source.Rows.Count colCount = Source.Columns.Count ReDim Result(1 To rowCount, 1 To colCount) As Variant 'Load result array For i = 1 To rowCount For j = 1 To colCount Result(i, j) = Source(i, j).Value Next j Next i 'Return result array Range2Array = Result End Function '-- 'The shorter version... Function Range22Array(ByRef Source As Range) As Variant Dim Result As Variant If TypeName(Selection) < "Range" Then Exit Function Result = Source.Value Range22Array = Result End Function -- Jim Cone Portland, Oregon USA "Spatters71" wrote in message I am just trying to create an array function that returns an array containing the values in a source Range. It shouldn't be that difficult, right? My source is below. I am running into the problem on this line: "Result(i, j) = Source(i, j).Value". The Result element is Empty after the assignment, even though Source(i,j).Value is not Empty. Help!? If you know why this assignment isn't working or if you know a better way to do this, please let me know. Function Range2Array(Source As Range) As Variant Dim rowCount, colCount As Integer rowCount = Source.Rows.count colCount = Source.Columns.count ' Ensure target is a range equal in size to source If Not (TypeOf Application.Caller Is Range _ And Application.Caller.Columns.count = colCount _ And Application.Caller.Rows.count = rowCount _ ) Then Exit Function End If ' Load result array Dim Result() As Variant ReDim Result(1 To rowCount, 1 To colCount) As Variant Dim i, j As Integer Result(1, 1) = "Test" For i = 1 To rowCount For j = 1 To colCount ' At this point Source(i,j).Value is not Empty Result(i, j) = Source(i, j).Value ' At this point the value of Result is Empty. Next j Next i ' Return result array Test = Result End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Array Function...What's wrong?
I think that you missed the most important line in your code. You have to tell
it what the function returns: Range2Array = Result instead of: Test = Result Option Explicit Function Range2Array(Source As Range) As Variant Dim rowCount As Long Dim colCount As Integer Dim Result() As Variant Dim i As Long Dim j As Long 'single area check Set Source = Source.Areas(1) rowCount = Source.Rows.Count colCount = Source.Columns.Count ' Ensure target is a range equal in size to source If TypeOf Application.Caller Is Range Then If Application.Caller.Columns.Count < colCount _ And Application.Caller.Rows.Count < rowCount Then Range2Array = CVErr(xlErrRef) Exit Function End If End If ' Load result array ReDim Result(1 To rowCount, 1 To colCount) 'Result(1, 1) = "Test" For i = 1 To rowCount For j = 1 To colCount ' At this point Source(i,j).Value is not Empty Result(i, j) = Source(i, j).Value ' At this point the value of Result is Empty. Next j Next i ' Return result array Range2Array = Result End Function I also declared your variables as long instead of integers and variants. dim i,j as long declares i as a variant and j as a long. ====== And if you really just wanted to pick up the values in a single area range, you could use: dim myArr as variant 'no ()'s here myarr = source.value or myarr = source.areas(1).value This results in a two dimensional array (x rows by y columns). It's even 2 dimensional if you passed it a single column (x rows by 1 column). Kind of like: dim myArr(1 to 99, 1 to 1) Spatters71 wrote: I am just trying to create an array function that returns an array containing the values in a source Range. It shouldn't be that difficult, right? My source is below. I am running into the problem on this line: "Result(i, j) = Source(i, j).Value". The Result element is Empty after the assignment, even though Source(i,j).Value is not Empty. Help!? If you know why this assignment isn't working or if you know a better way to do this, please let me know. Function Range2Array(Source As Range) As Variant Dim rowCount, colCount As Integer rowCount = Source.Rows.count colCount = Source.Columns.count ' Ensure target is a range equal in size to source If Not (TypeOf Application.Caller Is Range _ And Application.Caller.Columns.count = colCount _ And Application.Caller.Rows.count = rowCount _ ) Then Exit Function End If ' Load result array Dim Result() As Variant ReDim Result(1 To rowCount, 1 To colCount) As Variant Dim i, j As Integer Result(1, 1) = "Test" For i = 1 To rowCount For j = 1 To colCount ' At this point Source(i,j).Value is not Empty Result(i, j) = Source(i, j).Value ' At this point the value of Result is Empty. Next j Next i ' Return result array Test = Result End Function -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Array Function...What's wrong?
Good answers, all. FYI the "Test =" line was just a symptom of the fact that
I renamed my Function from "Test" before I posted it. Range.Value returns an array, that's handy :) Anyway, thanks, I'll try these mods in the morning. "Dave Peterson" wrote: I think that you missed the most important line in your code. You have to tell it what the function returns: Range2Array = Result instead of: Test = Result Option Explicit Function Range2Array(Source As Range) As Variant Dim rowCount As Long Dim colCount As Integer Dim Result() As Variant Dim i As Long Dim j As Long 'single area check Set Source = Source.Areas(1) rowCount = Source.Rows.Count colCount = Source.Columns.Count ' Ensure target is a range equal in size to source If TypeOf Application.Caller Is Range Then If Application.Caller.Columns.Count < colCount _ And Application.Caller.Rows.Count < rowCount Then Range2Array = CVErr(xlErrRef) Exit Function End If End If ' Load result array ReDim Result(1 To rowCount, 1 To colCount) 'Result(1, 1) = "Test" For i = 1 To rowCount For j = 1 To colCount ' At this point Source(i,j).Value is not Empty Result(i, j) = Source(i, j).Value ' At this point the value of Result is Empty. Next j Next i ' Return result array Range2Array = Result End Function I also declared your variables as long instead of integers and variants. dim i,j as long declares i as a variant and j as a long. ====== And if you really just wanted to pick up the values in a single area range, you could use: dim myArr as variant 'no ()'s here myarr = source.value or myarr = source.areas(1).value This results in a two dimensional array (x rows by y columns). It's even 2 dimensional if you passed it a single column (x rows by 1 column). Kind of like: dim myArr(1 to 99, 1 to 1) Spatters71 wrote: I am just trying to create an array function that returns an array containing the values in a source Range. It shouldn't be that difficult, right? My source is below. I am running into the problem on this line: "Result(i, j) = Source(i, j).Value". The Result element is Empty after the assignment, even though Source(i,j).Value is not Empty. Help!? If you know why this assignment isn't working or if you know a better way to do this, please let me know. Function Range2Array(Source As Range) As Variant Dim rowCount, colCount As Integer rowCount = Source.Rows.count colCount = Source.Columns.count ' Ensure target is a range equal in size to source If Not (TypeOf Application.Caller Is Range _ And Application.Caller.Columns.count = colCount _ And Application.Caller.Rows.count = rowCount _ ) Then Exit Function End If ' Load result array Dim Result() As Variant ReDim Result(1 To rowCount, 1 To colCount) As Variant Dim i, j As Integer Result(1, 1) = "Test" For i = 1 To rowCount For j = 1 To colCount ' At this point Source(i,j).Value is not Empty Result(i, j) = Source(i, j).Value ' At this point the value of Result is Empty. Next j Next i ' Return result array Test = Result End Function -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum + IF Array wrong sum | Excel Worksheet Functions | |||
weekday function in excel 2007 wrong | Excel Discussion (Misc queries) | |||
What's wrong with my array formula | Excel Discussion (Misc queries) | |||
What is wrong with my array? | Excel Programming | |||
Please help - What am i doing wrong with this array? | Excel Programming |