I have a string array, declared by:

Dim HRColHdrs(3) As Variant

I explicitly define the strings, then pass them to a function, as "ColHdrs" shown below.

Private Sub HdrChk(ByVal ShtName As String, ByRef ColHdrs As Variant)
Dim i, n As Long

Debug.Print (Len(ColHdrs))

End Sub

Within this function, I would like to determine how many elements are in this array that was passed. However, when I use the len() function, I get Type mismatch error.

Things I have tried:
-Declaring "HRColHdrs" as an array of type String in the main function (no change in error)
-Declaring the "ColHdrs" as a variable of type String or Object rather than Variant
-Declaring "ColHdrs" as a ByVal variable
-Using the ColHdrs.Length method (error changes to "Object required")
-I used debug.print to make sure that I can read the individual elements of ColHdrs in the subroutine - I can.

Oh, if only every language handled arrays like Matlab scripting :-/.

The only workaround I can think of is to step through a For loop and address each element of the array directly, until it throws a subscript error, then use the error handler to extract from the loop.

EggHeadCafe - Software Developer Portal of Choice
ASP.NET 2.0: Using the Menu Control
http://www.eggheadcafe.com/tutorials...ng-the-me.aspx

Hi Craig,

try Debug.Print (cstr(Ubound(ColHdrs)))

Regards,

Rainer


<Craig Remillard schrieb im Newsbeitrag ...
I have a string array, declared by:

Dim HRColHdrs(3) As Variant

I explicitly define the strings, then pass them to a function, as "ColHdrs" shown below.

Private Sub HdrChk(ByVal ShtName As String, ByRef ColHdrs As Variant)
Dim i, n As Long

Debug.Print (Len(ColHdrs))

End Sub

Within this function, I would like to determine how many elements are in this array that was passed. However, when I use the
len() function, I get Type mismatch error.

Things I have tried:
-Declaring "HRColHdrs" as an array of type String in the main function (no change in error)
-Declaring the "ColHdrs" as a variable of type String or Object rather than Variant
-Declaring "ColHdrs" as a ByVal variable
-Using the ColHdrs.Length method (error changes to "Object required")
-I used debug.print to make sure that I can read the individual elements of ColHdrs in the subroutine - I can.

Oh, if only every language handled arrays like Matlab scripting :-/.

The only workaround I can think of is to step through a For loop and address each element of the array directly, until it throws
a subscript error, then use the error handler to extract from the loop.

EggHeadCafe - Software Developer Portal of Choice
ASP.NET 2.0: Using the Menu Control
http://www.eggheadcafe.com/tutorials...ng-the-me.aspx

Debug.Print (UBound(ColHdrs)-LBound(ColHdrs)+1)

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

<Craig Remillard wrote in message
...
I have a string array, declared by:

Dim HRColHdrs(3) As Variant

I explicitly define the strings, then pass them to a function, as
"ColHdrs" shown below.

Private Sub HdrChk(ByVal ShtName As String, ByRef ColHdrs As Variant)
Dim i, n As Long

Debug.Print (Len(ColHdrs))

End Sub

Within this function, I would like to determine how many elements are in
this array that was passed. However, when I use the len() function, I get
Type mismatch error.

Things I have tried:
-Declaring "HRColHdrs" as an array of type String in the main function (no
change in error)
-Declaring the "ColHdrs" as a variable of type String or Object rather
than Variant
-Declaring "ColHdrs" as a ByVal variable
-Using the ColHdrs.Length method (error changes to "Object required")
-I used debug.print to make sure that I can read the individual elements
of ColHdrs in the subroutine - I can.

Oh, if only every language handled arrays like Matlab scripting :-/.

The only workaround I can think of is to step through a For loop and
address each element of the array directly, until it throws a subscript
error, then use the error handler to extract from the loop.

EggHeadCafe - Software Developer Portal of Choice
ASP.NET 2.0: Using the Menu Control
http://www.eggheadcafe.com/tutorials...ng-the-me.aspx

Hi Craig

If you want to determine how many elements that has been loaded into your
array, I think this is what you need:

Dim HRColHdrs(3) As Variant

Private Sub HdrChk(ByVal ShtName As String, ByRef ColHdrs As Variant)
Dim i As Long, n As Long
For i = LBound(ColHdrs) To UBound(ColHdrs)
If ColHdrs(i) < "" Then
n = n + 1
End If
Next
Debug.Print n & " elements are in this array"
End Sub

Regards,
Per

"Craig Remillard" skrev i meddelelsen
...
I have a string array, declared by:

Dim HRColHdrs(3) As Variant

I explicitly define the strings, then pass them to a function, as
"ColHdrs" shown below.

Private Sub HdrChk(ByVal ShtName As String, ByRef ColHdrs As Variant)
Dim i, n As Long

Debug.Print (Len(ColHdrs))

End Sub

Within this function, I would like to determine how many elements are in
this array that was passed. However, when I use the len() function, I get
Type mismatch error.

Things I have tried:
-Declaring "HRColHdrs" as an array of type String in the main function (no
change in error)
-Declaring the "ColHdrs" as a variable of type String or Object rather
than Variant
-Declaring "ColHdrs" as a ByVal variable
-Using the ColHdrs.Length method (error changes to "Object required")
-I used debug.print to make sure that I can read the individual elements
of ColHdrs in the subroutine - I can.

Oh, if only every language handled arrays like Matlab scripting :-/.

The only workaround I can think of is to step through a For loop and
address each element of the array directly, until it throws a subscript
error, then use the error handler to extract from the loop.

EggHeadCafe - Software Developer Portal of Choice
ASP.NET 2.0: Using the Menu Control
http://www.eggheadcafe.com/tutorials...ng-the-me.aspx

As ColHdrs is declared as a variant array you can simply do:

Debug.Print Application.WorksheetFunction.CountA(ColHdrs)

So, no need for the loop.


RBS


"Per Jessen" wrote in message
...
Hi Craig

If you want to determine how many elements that has been loaded into your
array, I think this is what you need:

Dim HRColHdrs(3) As Variant

Private Sub HdrChk(ByVal ShtName As String, ByRef ColHdrs As Variant)
Dim i As Long, n As Long
For i = LBound(ColHdrs) To UBound(ColHdrs)
If ColHdrs(i) < "" Then
n = n + 1
End If
Next
Debug.Print n & " elements are in this array"
End Sub

Regards,
Per

"Craig Remillard" skrev i meddelelsen
...
I have a string array, declared by:

Dim HRColHdrs(3) As Variant

I explicitly define the strings, then pass them to a function, as
"ColHdrs" shown below.

Private Sub HdrChk(ByVal ShtName As String, ByRef ColHdrs As Variant)
Dim i, n As Long

Debug.Print (Len(ColHdrs))

End Sub

Within this function, I would like to determine how many elements are in
this array that was passed. However, when I use the len() function, I
get Type mismatch error.

Things I have tried:
-Declaring "HRColHdrs" as an array of type String in the main function
(no change in error)
-Declaring the "ColHdrs" as a variable of type String or Object rather
than Variant
-Declaring "ColHdrs" as a ByVal variable
-Using the ColHdrs.Length method (error changes to "Object required")
-I used debug.print to make sure that I can read the individual elements
of ColHdrs in the subroutine - I can.

Oh, if only every language handled arrays like Matlab scripting :-/.

The only workaround I can think of is to step through a For loop and
address each element of the array directly, until it throws a subscript
error, then use the error handler to extract from the loop.

EggHeadCafe - Software Developer Portal of Choice
ASP.NET 2.0: Using the Menu Control
http://www.eggheadcafe.com/tutorials...ng-the-me.aspx

Thanks to everyone for your replies. Once I had UBound and LBound it was easy. For reference, here is the (much less elegant) solution I had originally come up with using error handlers. I worked, but ugh.

===========================

Private Function HdrChk(ByVal ShtName As String, ByVal ColHdrs As Variant) As Boolean
Dim i, d As Long

'set an offset value in case lbound < 1
d = 1 - LBound(ColHdrs)

'fill top row with header labels
For i = LBound(ColHdrs) To UBound(ColHdrs)
Worksheets(ShtName).Cells(1, i + d).Value = ColHdrs(i)
Next i

'return true. This boolean is currently a programming
'artifact, but it has been kept in case some error
'handling is desired.
HdrChk = True
Exit Function
End Function

Private Function XHdrChkX(ByVal ShtName As String, ByVal ColHdrs As Variant) As Boolean
Dim i As Long

On Error GoTo ErrHdlr

i = 1
Do Until True
Worksheets(ShtName).Cells(1, i).Value = ColHdrs(i)
i = i + 1
Loop

LoopExit:

XHdrChkX = True

Exit Function

ErrHdlr:
If Err = 9 Then
GoTo LoopExit
Else
XHdrChkX = False
Exit Function
End If


End Function



Per Jessen wrote:

Hi CraigIf you want to determine how many elements that has been loaded into
22-Nov-09

Hi Craig

If you want to determine how many elements that has been loaded into your
array, I think this is what you need:

Dim HRColHdrs(3) As Variant

Private Sub HdrChk(ByVal ShtName As String, ByRef ColHdrs As Variant)
Dim i As Long, n As Long
For i = LBound(ColHdrs) To UBound(ColHdrs)
If ColHdrs(i) < "" Then
n = n + 1
End If
Next
Debug.Print n & " elements are in this array"
End Sub

Regards,
Per

"Craig Remillard" skrev i meddelelsen

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
SQL Injection - SQL Server Convert EXEC To Avoid Attacks
http://www.eggheadcafe.com/tutorials...sql-serve.aspx

Sorry, that was both the function that I ended up using (HdrChk), and the original function with error handler loop escape (XHdrChkX). The latter now goes to the graveyard!



Craig Remillard wrote:

Solution taken
23-Nov-09

Thanks to everyone for your replies. Once I had UBound and LBound it was easy. For reference, here is the (much less elegant) solution I had originally come up with using error handlers. I worked, but ugh.

===========================

Private Function HdrChk(ByVal ShtName As String, ByVal ColHdrs As Variant) As Boolean
Dim i, d As Long

'set an offset value in case lbound < 1
d = 1 - LBound(ColHdrs)

'fill top row with header labels
For i = LBound(ColHdrs) To UBound(ColHdrs)
Worksheets(ShtName).Cells(1, i + d).Value = ColHdrs(i)
Next i

'return true. This boolean is currently a programming
'artifact, but it has been kept in case some error
'handling is desired.
HdrChk = True
Exit Function
End Function

Private Function XHdrChkX(ByVal ShtName As String, ByVal ColHdrs As Variant) As Boolean
Dim i As Long

On Error GoTo ErrHdlr

i = 1
Do Until True
Worksheets(ShtName).Cells(1, i).Value = ColHdrs(i)
i = i + 1
Loop

LoopExit:

XHdrChkX = True

Exit Function

ErrHdlr:
If Err = 9 Then
GoTo LoopExit
Else
XHdrChkX = False
Exit Function
End If


End Function

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Map Stored Procedure Output To Class Properties
http://www.eggheadcafe.com/tutorials...dure-outp.aspx