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 news:(E-Mail Removed)...
>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
news:(E-Mail Removed)...
>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
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:unw%(E-Mail Removed)...
> 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
> news:(E-Mail Removed)...
>>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 Crai

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

Dim HRColHdrs(3) As Varian

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

Regards
Pe

"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