Can't get length of a variant array passed to a subroutine

[Craig Remillard]

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/tutorial...b210-d64f9d7293b6/aspnet-20-using-the-me.aspx

 

[Rainer Bielefeld]

Hi Craig,

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

Regards,

Rainer

 

[Charles Williams]

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

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

 

[Per Jessen]

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:[email protected]...

 

[RB Smissaert]

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

 

[Craig Remillard]

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/tutorial...b4-e23c743ddfc2/sql-injection--sql-serve.aspx

 

[Craig Remillard]

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/tutorial...e-46847fa770ef/map-stored-procedure-outp.aspx