Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
I've got the following function that I would like to make versatile enough
that I can use it within VBA code and as a simple excel worksheet function. I've pasted the function here where I'm having trouble, I need to figure out how to count the elements in the range for both types of data. The "Size" variable where I'm getting the dimension works with the UBound if my data type is an array or variant, but I have to use Xrange.Count if the data is an excel workbook range. Neither option works for both instances. Here is my code: Sub TestFunc() Dim Test1(3) As Single, Test2(3) As Single, Value As Single, Value2 As Single Test1(1) = 3 Test1(2) = 2 Test1(3) = 1 Test2(1) = 1 Test2(2) = 2 Test2(3) = 3 Value = 2.5 Value2 = interpolate(Value, Test1, Test2) Worksheets("Input Page").Select Range("P25") = Value2 End Sub Function interpolate(Value, xrange, yrange) ' Function linearly interpolates from a given x/y range ' Function allows for extrapolation outside the known range ' Dim Size As Integer, Cumiminus1 As Single, Cumi As Single Dim X1 As Single, X2 As Single, Y1 As Single, Y2 As Single ' ' Size = xrange.Count ' Size = 3 Size = UBound(xrange) ' Thanks, Tom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
The declaration of xrange must be an array for UBound to work, now it is a
Variant type containing an array. If xrange is not always containing Single values you can declare it as an array of Variant like this: Function interpolate(Value, xrange(), yrange) If xrange is always containing Single values you are better to declare it as an array of Single like this: Function interpolate(Value, xrange() as Single, yrange) "TomLegado" wrote in message ... I've got the following function that I would like to make versatile enough that I can use it within VBA code and as a simple excel worksheet function. I've pasted the function here where I'm having trouble, I need to figure out how to count the elements in the range for both types of data. The "Size" variable where I'm getting the dimension works with the UBound if my data type is an array or variant, but I have to use Xrange.Count if the data is an excel workbook range. Neither option works for both instances. Here is my code: Sub TestFunc() Dim Test1(3) As Single, Test2(3) As Single, Value As Single, Value2 As Single Test1(1) = 3 Test1(2) = 2 Test1(3) = 1 Test2(1) = 1 Test2(2) = 2 Test2(3) = 3 Value = 2.5 Value2 = interpolate(Value, Test1, Test2) Worksheets("Input Page").Select Range("P25") = Value2 End Sub Function interpolate(Value, xrange, yrange) ' Function linearly interpolates from a given x/y range ' Function allows for extrapolation outside the known range ' Dim Size As Integer, Cumiminus1 As Single, Cumi As Single Dim X1 As Single, X2 As Single, Y1 As Single, Y2 As Single ' ' Size = xrange.Count ' Size = 3 Size = UBound(xrange) ' Thanks, Tom |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
Alain, thank you very much for your quick reply.
OK so I can make it work nicely for the instance that I'm passing an array in from another sub-routine, but what about the instance where I use the function as a worksheet function with excel range syntax as the argument. This is where I'm having trouble because VBA can not give me the bounds on this variant. Is there any way to make this function work for both cases? "Alain Vaillancourt" wrote: The declaration of xrange must be an array for UBound to work, now it is a Variant type containing an array. If xrange is not always containing Single values you can declare it as an array of Variant like this: Function interpolate(Value, xrange(), yrange) If xrange is always containing Single values you are better to declare it as an array of Single like this: Function interpolate(Value, xrange() as Single, yrange) "TomLegado" wrote in message ... I've got the following function that I would like to make versatile enough that I can use it within VBA code and as a simple excel worksheet function. I've pasted the function here where I'm having trouble, I need to figure out how to count the elements in the range for both types of data. The "Size" variable where I'm getting the dimension works with the UBound if my data type is an array or variant, but I have to use Xrange.Count if the data is an excel workbook range. Neither option works for both instances. Here is my code: Sub TestFunc() Dim Test1(3) As Single, Test2(3) As Single, Value As Single, Value2 As Single Test1(1) = 3 Test1(2) = 2 Test1(3) = 1 Test2(1) = 1 Test2(2) = 2 Test2(3) = 3 Value = 2.5 Value2 = interpolate(Value, Test1, Test2) Worksheets("Input Page").Select Range("P25") = Value2 End Sub Function interpolate(Value, xrange, yrange) ' Function linearly interpolates from a given x/y range ' Function allows for extrapolation outside the known range ' Dim Size As Integer, Cumiminus1 As Single, Cumi As Single Dim X1 As Single, X2 As Single, Y1 As Single, Y2 As Single ' ' Size = xrange.Count ' Size = 3 Size = UBound(xrange) ' Thanks, Tom |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
Assuming that your arrays are 1-based, 1-dimensional (or single column)
arrays, you might consider the folowing snippet: If IsArray(xrange) Then If TypeOf xrange Is Range Then Size = xrange.Count Else Size = UBound(xrange) End If Else Msg = "Input must be an array or multi-celled range" MsgBox Msg, 16 End If Alan Beban TomLegado wrote: I've got the following function that I would like to make versatile enough that I can use it within VBA code and as a simple excel worksheet function. I've pasted the function here where I'm having trouble, I need to figure out how to count the elements in the range for both types of data. The "Size" variable where I'm getting the dimension works with the UBound if my data type is an array or variant, but I have to use Xrange.Count if the data is an excel workbook range. Neither option works for both instances. Here is my code: Sub TestFunc() Dim Test1(3) As Single, Test2(3) As Single, Value As Single, Value2 As Single Test1(1) = 3 Test1(2) = 2 Test1(3) = 1 Test2(1) = 1 Test2(2) = 2 Test2(3) = 3 Value = 2.5 Value2 = interpolate(Value, Test1, Test2) Worksheets("Input Page").Select Range("P25") = Value2 End Sub Function interpolate(Value, xrange, yrange) ' Function linearly interpolates from a given x/y range ' Function allows for extrapolation outside the known range ' Dim Size As Integer, Cumiminus1 As Single, Cumi As Single Dim X1 As Single, X2 As Single, Y1 As Single, Y2 As Single ' ' Size = xrange.Count ' Size = 3 Size = UBound(xrange) ' Thanks, Tom |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
Sorry I think I answered to quickly, if you need to pass an array or Excel
range you keep it in a Variant and use the IsArray and TypeOf to handle the difference between them. The next message from Alan in this thread give you a good example of how to do it... "TomLegado" wrote in message ... Alain, thank you very much for your quick reply. OK so I can make it work nicely for the instance that I'm passing an array in from another sub-routine, but what about the instance where I use the function as a worksheet function with excel range syntax as the argument. This is where I'm having trouble because VBA can not give me the bounds on this variant. Is there any way to make this function work for both cases? "Alain Vaillancourt" wrote: The declaration of xrange must be an array for UBound to work, now it is a Variant type containing an array. If xrange is not always containing Single values you can declare it as an array of Variant like this: Function interpolate(Value, xrange(), yrange) If xrange is always containing Single values you are better to declare it as an array of Single like this: Function interpolate(Value, xrange() as Single, yrange) "TomLegado" wrote in message ... I've got the following function that I would like to make versatile enough that I can use it within VBA code and as a simple excel worksheet function. I've pasted the function here where I'm having trouble, I need to figure out how to count the elements in the range for both types of data. The "Size" variable where I'm getting the dimension works with the UBound if my data type is an array or variant, but I have to use Xrange.Count if the data is an excel workbook range. Neither option works for both instances. Here is my code: Sub TestFunc() Dim Test1(3) As Single, Test2(3) As Single, Value As Single, Value2 As Single Test1(1) = 3 Test1(2) = 2 Test1(3) = 1 Test2(1) = 1 Test2(2) = 2 Test2(3) = 3 Value = 2.5 Value2 = interpolate(Value, Test1, Test2) Worksheets("Input Page").Select Range("P25") = Value2 End Sub Function interpolate(Value, xrange, yrange) ' Function linearly interpolates from a given x/y range ' Function allows for extrapolation outside the known range ' Dim Size As Integer, Cumiminus1 As Single, Cumi As Single Dim X1 As Single, X2 As Single, Y1 As Single, Y2 As Single ' ' Size = xrange.Count ' Size = 3 Size = UBound(xrange) ' Thanks, Tom |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
Alan, thanks that works very well.
"Alan Beban" wrote: Assuming that your arrays are 1-based, 1-dimensional (or single column) arrays, you might consider the folowing snippet: If IsArray(xrange) Then If TypeOf xrange Is Range Then Size = xrange.Count Else Size = UBound(xrange) End If Else Msg = "Input must be an array or multi-celled range" MsgBox Msg, 16 End If Alan Beban TomLegado wrote: I've got the following function that I would like to make versatile enough that I can use it within VBA code and as a simple excel worksheet function. I've pasted the function here where I'm having trouble, I need to figure out how to count the elements in the range for both types of data. The "Size" variable where I'm getting the dimension works with the UBound if my data type is an array or variant, but I have to use Xrange.Count if the data is an excel workbook range. Neither option works for both instances. Here is my code: Sub TestFunc() Dim Test1(3) As Single, Test2(3) As Single, Value As Single, Value2 As Single Test1(1) = 3 Test1(2) = 2 Test1(3) = 1 Test2(1) = 1 Test2(2) = 2 Test2(3) = 3 Value = 2.5 Value2 = interpolate(Value, Test1, Test2) Worksheets("Input Page").Select Range("P25") = Value2 End Sub Function interpolate(Value, xrange, yrange) ' Function linearly interpolates from a given x/y range ' Function allows for extrapolation outside the known range ' Dim Size As Integer, Cumiminus1 As Single, Cumi As Single Dim X1 As Single, X2 As Single, Y1 As Single, Y2 As Single ' ' Size = xrange.Count ' Size = 3 Size = UBound(xrange) ' Thanks, Tom |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
Two clarifying points.
First, it is not necessary to keep the array in a Variant variable; the following works fine: Sub abtest3() Dim arr() As Single, rng As Range Set rng = Range("C1:C14") ReDim arr(1 To rng.Count, 1 To 1) For i = 1 To rng.Count arr(i, 1) = rng(i) Next Size = getSize(arr) Debug.Print Size, TypeName(arr) '<----Returns 14 Single() End Sub Function getSize(xyz) If IsArray(xyz) Then If TypeOf xyz Is Range Then getSize = xyz.Count Else getSize = UBound(xyz) End If Else Msg = "Input must be an array or multi-celled range" MsgBox Msg, 16 End If End Function Second, Alain's first response in this thread included the statement "The declaration of xrange must be an array for UBound to work, now it is a Variant type containing an array." In fact, UBound works fine on either an array contained within a Variant variable or a Variant() type array (or for that matter, any other type of VBA array, e.g., Integer(), Single(), etc.). Sub abtest4() Dim xrange1, xrange2() xrange1 = Range("C1:C14") xrange2 = Range("C1:C14") 'xrange1 is an array contained within a Variant type variable; 'xrange2 is a Variant() type array Debug.Print UBound(xrange1), UBound(xrange2) '<----Returns 14 14 End Sub Alan Beban Alain Vaillancourt wrote: Sorry I think I answered to quickly, if you need to pass an array or Excel range you keep it in a Variant and use the IsArray and TypeOf to handle the difference between them. The next message from Alan in this thread give you a good example of how to do it... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
To clarify points:
In that example xyz is a Variant, by default when you don't specify a type it is of type Variant Second, UBound only work for arrays so if you don't use type checking like TypeOf or IsArray you will have a problem using it on an Excel Range... "Alan Beban" wrote in message ... Two clarifying points. First, it is not necessary to keep the array in a Variant variable; the following works fine: Sub abtest3() Dim arr() As Single, rng As Range Set rng = Range("C1:C14") ReDim arr(1 To rng.Count, 1 To 1) For i = 1 To rng.Count arr(i, 1) = rng(i) Next Size = getSize(arr) Debug.Print Size, TypeName(arr) '<----Returns 14 Single() End Sub Function getSize(xyz) If IsArray(xyz) Then If TypeOf xyz Is Range Then getSize = xyz.Count Else getSize = UBound(xyz) End If Else Msg = "Input must be an array or multi-celled range" MsgBox Msg, 16 End If End Function Second, Alain's first response in this thread included the statement "The declaration of xrange must be an array for UBound to work, now it is a Variant type containing an array." In fact, UBound works fine on either an array contained within a Variant variable or a Variant() type array (or for that matter, any other type of VBA array, e.g., Integer(), Single(), etc.). Sub abtest4() Dim xrange1, xrange2() xrange1 = Range("C1:C14") xrange2 = Range("C1:C14") 'xrange1 is an array contained within a Variant type variable; 'xrange2 is a Variant() type array Debug.Print UBound(xrange1), UBound(xrange2) '<----Returns 14 14 End Sub Alan Beban Alain Vaillancourt wrote: Sorry I think I answered to quickly, if you need to pass an array or Excel range you keep it in a Variant and use the IsArray and TypeOf to handle the difference between them. The next message from Alan in this thread give you a good example of how to do it... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
Alain Vaillancourt wrote:
To clarify points: In that example xyz is a Variant, by default when you don't specify a type it is of type Variant If by "that example" you mean the code below in Sub abtest3 and Function getSize, xyz is of type Single(), as you can see if you insert just before If IsArray(xyz) the following: MsgBox Typename(xyz) xyz is simply a placeholder for the parameter "arr" that is passed from the sub procedure, and which is of type Single(). Second, UBound only work for arrays so if you don't use type checking like TypeOf or IsArray you will have a problem using it on an Excel Range... What it appeared you were saying in the portion of your first response in this thread that I quoted below was that UBound works only on an array of a built-in type (e.g., Variant(), Integer(), Single(), etc.) and not on an array contained within a Variant type variable--which is incorrect. Perhaps I misunderstood. Alan Beban "Alan Beban" wrote in message ... Two clarifying points. First, it is not necessary to keep the array in a Variant variable; the following works fine: Sub abtest3() Dim arr() As Single, rng As Range Set rng = Range("C1:C14") ReDim arr(1 To rng.Count, 1 To 1) For i = 1 To rng.Count arr(i, 1) = rng(i) Next Size = getSize(arr) Debug.Print Size, TypeName(arr) '<----Returns 14 Single() End Sub Function getSize(xyz) If IsArray(xyz) Then If TypeOf xyz Is Range Then getSize = xyz.Count Else getSize = UBound(xyz) End If Else Msg = "Input must be an array or multi-celled range" MsgBox Msg, 16 End If End Function Second, Alain's first response in this thread included the statement "The declaration of xrange must be an array for UBound to work, now it is a Variant type containing an array." In fact, UBound works fine on either an array contained within a Variant variable or a Variant() type array (or for that matter, any other type of VBA array, e.g., Integer(), Single(), etc.). Sub abtest4() Dim xrange1, xrange2() xrange1 = Range("C1:C14") xrange2 = Range("C1:C14") 'xrange1 is an array contained within a Variant type variable; 'xrange2 is a Variant() type array Debug.Print UBound(xrange1), UBound(xrange2) '<----Returns 14 14 End Sub Alan Beban Alain Vaillancourt wrote: Sorry I think I answered to quickly, if you need to pass an array or Excel range you keep it in a Variant and use the IsArray and TypeOf to handle the difference between them. The next message from Alan in this thread give you a good example of how to do it... |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
Yes I was meaning your code below. Type variable xyz is a Variant type,
Variant are container of other types and if you look at the documentation about the TypeName function they say that the required varname argument is a Variant. As I said in the second post I replied too quickly and was not meaning that UBound was not working with Variant at all but only with the Variant containing an array so if he would only use an array as a parameter it would have been better to declare it as an array. Since he want to also use an Excel Range as the parameter then the Variant is more appropriate with the use of IsArray, TypeOf or TypeName because UBound would not work with an Excel Range. "Alan Beban" wrote in message ... Alain Vaillancourt wrote: To clarify points: In that example xyz is a Variant, by default when you don't specify a type it is of type Variant If by "that example" you mean the code below in Sub abtest3 and Function getSize, xyz is of type Single(), as you can see if you insert just before If IsArray(xyz) the following: MsgBox Typename(xyz) xyz is simply a placeholder for the parameter "arr" that is passed from the sub procedure, and which is of type Single(). Second, UBound only work for arrays so if you don't use type checking like TypeOf or IsArray you will have a problem using it on an Excel Range... What it appeared you were saying in the portion of your first response in this thread that I quoted below was that UBound works only on an array of a built-in type (e.g., Variant(), Integer(), Single(), etc.) and not on an array contained within a Variant type variable--which is incorrect. Perhaps I misunderstood. Alan Beban "Alan Beban" wrote in message ... Two clarifying points. First, it is not necessary to keep the array in a Variant variable; the following works fine: Sub abtest3() Dim arr() As Single, rng As Range Set rng = Range("C1:C14") ReDim arr(1 To rng.Count, 1 To 1) For i = 1 To rng.Count arr(i, 1) = rng(i) Next Size = getSize(arr) Debug.Print Size, TypeName(arr) '<----Returns 14 Single() End Sub Function getSize(xyz) If IsArray(xyz) Then If TypeOf xyz Is Range Then getSize = xyz.Count Else getSize = UBound(xyz) End If Else Msg = "Input must be an array or multi-celled range" MsgBox Msg, 16 End If End Function Second, Alain's first response in this thread included the statement "The declaration of xrange must be an array for UBound to work, now it is a Variant type containing an array." In fact, UBound works fine on either an array contained within a Variant variable or a Variant() type array (or for that matter, any other type of VBA array, e.g., Integer(), Single(), etc.). Sub abtest4() Dim xrange1, xrange2() xrange1 = Range("C1:C14") xrange2 = Range("C1:C14") 'xrange1 is an array contained within a Variant type variable; 'xrange2 is a Variant() type array Debug.Print UBound(xrange1), UBound(xrange2) '<----Returns 14 14 End Sub Alan Beban Alain Vaillancourt wrote: Sorry I think I answered to quickly, if you need to pass an array or Excel range you keep it in a Variant and use the IsArray and TypeOf to handle the difference between them. The next message from Alan in this thread give you a good example of how to do it... |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
!!!Please, please, please insert
MsgBox Typename(xyz) as the first line of the Function getSize below and run Sub abtest3. You will see that that xyz IS NOT a variant type; it is of type String() Alan Beban Alain Vaillancourt wrote: Yes I was meaning your code below. Type variable xyz is a Variant type. . . Sub abtest3() Dim arr() As Single, rng As Range Set rng = Range("C1:C14") ReDim arr(1 To rng.Count, 1 To 1) For i = 1 To rng.Count arr(i, 1) = rng(i) Next Size = getSize(arr) Debug.Print Size, TypeName(arr) '<----Returns 14 Single() End Sub Function getSize(xyz) If IsArray(xyz) Then If TypeOf xyz Is Range Then getSize = xyz.Count Else getSize = UBound(xyz) End If Else Msg = "Input must be an array or multi-celled range" MsgBox Msg, 16 End If End Function |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
Please read the documentation carefully about the TypeName Function
Yes it return a String but about the type of variable contained in a Variant Please again just read the documentation for VBA Here is what they say but you can look also by yourself: TypeName Function : Returns a String that provides information about a variable. Syntax : TypeName(varname) The required varname argument is a Variant containing any variable except a variable of a user-defined type. I think you may not understand the purpose of the Variant type, it allow the variable to contain any type except user-defined type. Because you can't use the same functions on all the types they added functions like TypeName, IsArray, IsObject, etc. to allow conditional execution based on the type of variable contained in the Variant. "Alan Beban" wrote in message ... !!!Please, please, please insert MsgBox Typename(xyz) as the first line of the Function getSize below and run Sub abtest3. You will see that that xyz IS NOT a variant type; it is of type String() Alan Beban Alain Vaillancourt wrote: Yes I was meaning your code below. Type variable xyz is a Variant type. . . Sub abtest3() Dim arr() As Single, rng As Range Set rng = Range("C1:C14") ReDim arr(1 To rng.Count, 1 To 1) For i = 1 To rng.Count arr(i, 1) = rng(i) Next Size = getSize(arr) Debug.Print Size, TypeName(arr) '<----Returns 14 Single() End Sub Function getSize(xyz) If IsArray(xyz) Then If TypeOf xyz Is Range Then getSize = xyz.Count Else getSize = UBound(xyz) End If Else Msg = "Input must be an array or multi-celled range" MsgBox Msg, 16 End If End Function |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
I am indeed confused. Are you saying that, e.g., in the following code
Set rng = Range("A1:C3") Debug.Print Typename(rng) rng is a Variant? Alan Beban Alain Vaillancourt wrote: Please read the documentation carefully about the TypeName Function Yes it return a String but about the type of variable contained in a Variant Please again just read the documentation for VBA Here is what they say but you can look also by yourself: TypeName Function : Returns a String that provides information about a variable. Syntax : TypeName(varname) The required varname argument is a Variant containing any variable except a variable of a user-defined type. I think you may not understand the purpose of the Variant type, it allow the variable to contain any type except user-defined type. Because you can't use the same functions on all the types they added functions like TypeName, IsArray, IsObject, etc. to allow conditional execution based on the type of variable contained in the Variant. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting elements of array and range type data
I don't see a declaration line for rng so if it is not declared explicitly
as a range it is a Variant containing a range... "Alan Beban" wrote in message ... I am indeed confused. Are you saying that, e.g., in the following code Set rng = Range("A1:C3") Debug.Print Typename(rng) rng is a Variant? Alan Beban Alain Vaillancourt wrote: Please read the documentation carefully about the TypeName Function Yes it return a String but about the type of variable contained in a Variant Please again just read the documentation for VBA Here is what they say but you can look also by yourself: TypeName Function : Returns a String that provides information about a variable. Syntax : TypeName(varname) The required varname argument is a Variant containing any variable except a variable of a user-defined type. I think you may not understand the purpose of the Variant type, it allow the variable to contain any type except user-defined type. Because you can't use the same functions on all the types they added functions like TypeName, IsArray, IsObject, etc. to allow conditional execution based on the type of variable contained in the Variant. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the Number of Elements in an Array | Excel Discussion (Misc queries) | |||
counting elements of two arrays | Excel Programming | |||
Array loses data type when placed into range | Excel Programming | |||
What is the maximum allowed number of data elements in a data array? | Excel Discussion (Misc queries) | |||
Counting the number of elements within an array that have data | Excel Programming |