Passing an optional array into a sub 
Author Message
 Passing an optional array into a sub

Hello,
I have a generic Excel Merge rountine in a class modual that loops through
all the fields in the database and checked to see if it finds the names in a
spreadsheet and if it finds it replaces it with data from the DB.  Now  I'm
using this generic code but my users want to check one field and if the
value meets their criteria do something if not then don't. if the value in
the field building_class = R0 up to R9 then I want to execute some code. Now
to keep it generic as possible someone suugested passing the field value I
want to test as an optional argument. But here is the problem I need to
check for that whole R0-R9 range.  I know I have used ParamArray before to
do this but VB doesn't allow you to make this optional.  Does anyone know
how I can do this? I pasted the code that I have in my sub.  I left the
Optional ParamArray there even though I get an error just for example thanks

Public Sub MergeExcel(Rs As ADODB.Recordset, DataBaseConn As
ADODB.Connection, Optional ParamArray fldException())
    Dim myRange, myrange2, myrange3, myrange4 As Object
    Dim FindResults
    Dim myResults As Object
    Dim fld As ADODB.Field
    Set myRange = MyExcelApp.ActiveSheet.Cells
    For Each fld In Rs.Fields
        if fld.value <> fldException then
            Set FindResults = myRange.Find("##" & fld.Name & "##")
            If Not FindResults Is Nothing Then
                If Not IsNull(fld.Value) Then
                    myRange.Replace "##" & fld.Name & "##", fld.Value
                Else
                    myRange.Replace "##" & fld.Name & "##", ""
                End If
            Else
            End If
        else
            'code here to be executed if the fld.value  equels the value
R0,R1,R2,R3,R4,R5,R6,R7,R8,R9
    Next fld
End Sub



Tue, 13 Jul 2004 23:01:02 GMT  
 Passing an optional array into a sub
One way is to just use a Variant
'============
Option Explicit

Private msTestArray(4) As String

Private Sub Form_Load()
   msTestArray(0) = "Element 0"
   msTestArray(1) = "Element 1"
   msTestArray(2) = "Element 2"
   msTestArray(3) = "Element 3"
   msTestArray(4) = "Element 4"
   Call MySub(3, msTestArray)
   Call MySub(4)
End Sub

Private Sub MySub(Parm1 As Integer, Optional ArrayParm As Variant)
   Dim i As Integer
   Debug.Print Parm1

   If Not IsMissing(ArrayParm) Then
      For i = 0 To Parm1
         Debug.Print ArrayParm(i)
      Next
   End If

End Sub
'============

--
Ken Halter
MS-MVP-VB
Please keep it in the groups..


Quote:
> Hello,
> I have a generic Excel Merge rountine in a class modual that loops through
> all the fields in the database and checked to see if it finds the names in
a
> spreadsheet and if it finds it replaces it with data from the DB.  Now
I'm
> using this generic code but my users want to check one field and if the
> value meets their criteria do something if not then don't. if the value in
> the field building_class = R0 up to R9 then I want to execute some code.
Now
> to keep it generic as possible someone suugested passing the field value I
> want to test as an optional argument. But here is the problem I need to
> check for that whole R0-R9 range.  I know I have used ParamArray before to
> do this but VB doesn't allow you to make this optional.  Does anyone know
> how I can do this? I pasted the code that I have in my sub.  I left the
> Optional ParamArray there even though I get an error just for example
thanks

> Public Sub MergeExcel(Rs As ADODB.Recordset, DataBaseConn As
> ADODB.Connection, Optional ParamArray fldException())
>     Dim myRange, myrange2, myrange3, myrange4 As Object
>     Dim FindResults
>     Dim myResults As Object
>     Dim fld As ADODB.Field
>     Set myRange = MyExcelApp.ActiveSheet.Cells
>     For Each fld In Rs.Fields
>         if fld.value <> fldException then
>             Set FindResults = myRange.Find("##" & fld.Name & "##")
>             If Not FindResults Is Nothing Then
>                 If Not IsNull(fld.Value) Then
>                     myRange.Replace "##" & fld.Name & "##", fld.Value
>                 Else
>                     myRange.Replace "##" & fld.Name & "##", ""
>                 End If
>             Else
>             End If
>         else
>             'code here to be executed if the fld.value  equels the value
> R0,R1,R2,R3,R4,R5,R6,R7,R8,R9
>     Next fld
> End Sub



Tue, 13 Jul 2004 23:28:03 GMT  
 Passing an optional array into a sub
Patrick,
    ParamArrays are implicitly optional.

Definition from VB Reference: "Optional array of Variant elements"
and goes on to say...
"The ParamArray keyword can't be used with ByVal, ByRef, or Optional."

    When using a "ParamArray", it is (by default) optional, variant, array.

Tim



Wed, 14 Jul 2004 03:16:29 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Passing (Optional) control to a Sub ?

2. pass an array as function/sub argument

3. Question on passing array parameters to VB.NET Sub/Function

4. Problem in passing datarow array from one sub to other

5. Passing Arrays from Sub to Program

6. Help! Problem passing string array to sub

7. Passing Property Arrays to Sub Routines

8. vb5 Passing an array of type MENU to a Sub

9. How to pass array into Sub?

10. Passing Control Arrays to Sub-Procedures

11. Newbie question about passing arrays into a sub routine

12. Passing array back from Sub/Function

 

 
Powered by phpBB® Forum Software