vba excel passing a dynamic number of parameter to a sub

Vba excel passing a dynamic number of parameter to a sub

I always wondered, when you program a sub and pass to it 3 parameters:

sub test(name, adress, phone)
   '=== process stuff
end sub

Then you call it several time in your program

call sub("wildboy", "555", "555-5555")

But then, you want to add one more parameter to the sub, but you have to change each line where you call the sub :(

I found how to bypass this with a type

Here is how i call a sub to format a range in excel and i can pass more or less parameters

Actually i pass the same number of parameters but shhh don't tell anyone

If i pass less parameters, i can always add a on error resume next to verify if the variable existe in the type
inside the sub

So i pass a variable of a certain TYPE to the sub

I can change the type number of variables without changing the call to the sub
and if i dont use a variable in the type in my sub, i just have to check if contain nothing

Of course by default, my sub must process empty variables of the type as a negative (0)

So the sub will process new variables i added to the type only if it contain something that will require a new processing in the sub without me having to change each line that call the sub in the main program
(read the precedent line twice if your head exploded the first time)

Tadaaaaaaaaaaaaaa!

First, define a type in excel vba:

'---------------- start code excel vba excel 2007 ALT F11 insert code


Type range_formatting_type
    '=== call rngfor(lineinside (0 or 1), rng01, linestyle (full line, half line etc), weight (thickness), color (foreground), colorindex (background))
    line_inside As Integer      '=== draw separative lines inside the range (0 = false)
    line_outside As Integer     '=== draw line outside (0 = false)
    range As Excel.range        '=== range to format and draw lines
    line_style As Integer       '=== 0 = half line
                                '=== 1 = plain line
    line_weight As Integer      '=== thickness?
    line_color As Integer       '=== line color
    cell_background As Integer  '=== colorindex, cell background color
    font_color As Integer       '=== color of letters inside the cell/range
End Type
'--------------- end code

'=== Before calling the sub, define a variable of this type:

'-------------- start code
sub main()

    Dim format01 As range_formatting_type

    format01.line_inside = 1
    format01.line_outside = 1
    Set format01.range = Excel.range(Cells(1, 1), Cells(10, 10))
    format01.line_style = 1
    format01.line_weight = 3
    format01.line_color = 1
    format01.cell_background = 35 'green
    format01.font_color = 0
 
    Call rangeformatting(format01)

end sub

'-------------- then the sub


Sub rangeformatting(format01 As range_formatting_type)
 
    'format01.line_inside = 0
    'format01.line_outside = 1
    'format01.range = rng01
    'format01.line_style = 1
    'format01.line_weight = 3
    'format01.line_color = 1
    'format01.cell_background = 35
    'format01.font_color = 0
 
    format01.range.Borders(xlEdgeLeft).LineStyle = format01.line_style
    format01.range.Borders(xlEdgeLeft).Weight = format01.line_weight
    format01.range.Borders(xlEdgeTop).LineStyle = format01.line_style
    format01.range.Borders(xlEdgeTop).Weight = format01.line_weight
    format01.range.Borders(xlEdgeBottom).LineStyle = format01.line_style
    format01.range.Borders(xlEdgeBottom).Weight = format01.line_weight
    format01.range.Borders(xlEdgeRight).LineStyle = format01.line_style
    format01.range.Borders(xlEdgeRight).Weight = format01.line_weight
 
    If format01.line_inside = 1 Then
        format01.range.Borders(xlInsideVertical).LineStyle = format01.line_style
        format01.range.Borders(xlInsideVertical).Weight = format01.line_weight
        format01.range.Borders(xlInsideHorizontal).LineStyle = format01.line_style
        format01.range.Borders(xlInsideHorizontal).Weight = format01.line_weight
    Else
        format01.range.Borders(xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone
        'rrng01.Borders(xlInsideVertical).Weight = 2
        format01.range.Borders(xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlLineStyleNone
        'rrng01.Borders(xlInsideHorizontal).Weight = 2
 
    End If
    '=== green
    'col01 = &H10C010
 
    format01.range.Borders(xlEdgeLeft).Color = format01.line_color
    format01.range.Borders(xlEdgeTop).Color = format01.line_color
    format01.range.Borders(xlEdgeBottom).Color = format01.line_color
    format01.range.Borders(xlEdgeRight).Color = format01.line_color
 
    format01.range.Interior.ColorIndex = format01.cell_background
End Sub

'----------------- end code