Passing an array to a function byref (default)

[Luke M]

Hey everyone,
I've been scouring forums and tutorials looking for the answer, but I have yet to find it. I have created an array that I assign the starting and ending cell values to when the user selects a checkbox (I used the checkbox changed event). The problem I'm having is with passing the array by reference into the function. I know that the code works without the array, but I need to clean it up so I'm not copy/pasting 20 lines of code for each checkbox changed event. Any help y'all can provide is much appreciated.

The purpose of coding the check boxes is to fill the back color of the row with green to signify it has been completed. This makes scanning the lists visually much faster.

I'm using windows 8.1 and excel 2013

' Array for passing cell values to the function
Dim cbxArray(1 To 2) As String

' Checbox changed event for worksheet
Private Sub CheckBox1_Change()
        cbxArray(1) = B3  
        cbxArray(2) = G3
    If CheckBox1.Value = True Then
        Change_Row_Color (cbxArray())
    Else
        Remove_Row_Color (cbxArray()) 
End Sub

' Function to change the color of the row when checkbox is selected
' Note: the code inside the function is generated via the excel macro recorder
Public Function Change_Row_Color(arr1 As String) As Integer
      Range("Cell & arr1(1):Cell & arr1(2)").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Function

' Function to remove any row color when the checkbox is deselected
Public Function Remove_Row_Color(arr2 As String) As String
    Range("Cell & arr2(1):Cell & arr2(2)").Select
     With Selection.Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .ThemeColor = xlThemeColorDark1
         .TintAndShade = 0
         .PatternTintAndShade = 0
     End With
End Function

 

[joeu2004]

The problem I'm having is with passing the array by reference into the function. [....] Any help y'all can provide is much appreciated.

It might simplest just to suggest one way to rewrite all this correctly. I will discuss key changes below. They are highlighted in red.

Option Explicit

' Array for passing cell values to the function
Dim cbxArray(1 To 2) As String

' Checbox changed event for worksheet
Private Sub CheckBox1_Change()
    cbxArray(1) = [COLOR=#ff0000]"B3"[/COLOR]
    cbxArray(2) = [COLOR=#ff0000]"G3"[/COLOR]
    If CheckBox1.Value = True Then
        Change_Row_Color [COLOR=#ff0000]cbxArray()[/COLOR]
    Else
        Remove_Row_Color [COLOR=#ff0000]cbxArray()[/COLOR]
    End If
End Sub

' Subroutine to change the color of the row when checkbox is selected
' Note: the code inside the function is generated via the excel macro recorder
Public [COLOR=#ff0000]Sub[/COLOR] Change_Row_Color(arr[COLOR=#ff0000]()[/COLOR] As String)
    With Range([COLOR=#ff0000]arr(1) & ":" & arr(2)[/COLOR]).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

' Subroutine to remove any row color when the checkbox is deselected
Public [COLOR=#ff0000]Sub[/COLOR] Remove_Row_Color(arr[COLOR=#ff0000]()[/COLOR] As String)
    With Range([COLOR=#ff0000]arr(1) & ":" & arr(2)[/COLOR]).Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .ThemeColor = xlThemeColorDark1
         .TintAndShade = 0
         .PatternTintAndShade = 0
     End With
End Sub

1. cbxArray(1) = "B3" andcbxArray(2) = "G3"

Since I do not see the VBA variables B3 and G3 in your code snippet, I assume you intend to refer to the strings "B3" and "G3".

That is consistent with your intended usage within the called procedures.


2. Change_Row_Color cbxArray() and Remove_Row_Color cbxArray()

Since you do not assign the function results to a variable -- and with my changes, we are not using the keyword Call -- the argument list must not be enclosed in parentheses.

If we used parentheses, the syntax would be interpreted as an expression, for example (A+B). In that case, VBA would signal a syntax error since cbxArray() cannot be operand of an expression.


3. Sub Change_Row_Color and Sub Remove_Row_Color

Since you do not assign result values to these procedure names, there is no need for them to be functions. It is not wrong; it just is not necessary.

Note: If this is just a code snippet and you do, in fact, assign result values to these procedure names in your full-length code, keep them as functions.


4. arr() As String

You must have parentheses after the variable name to indicate that it is an array. The argument "arr As String" declares arr as a simple string variable, not an array of strings.

Also, there is no need to use different names for the similar arguments of two different procedures. They are local variable names; that is, their scope is local.

On the contrary, it is "good practice" to use the same name for local variables that serve the same purpose in different procedures.


5. Range(arr(1) & ":" & arr(2))

This is the correct way to create the reference Range("B3:G3") in your case.

You wrote Range("Cell & arr1(1):Cell & arr1(2)"). That is syntactically incorrect, even if you correct it so that arr1(1) and arr1(2) are properly concatenated. That is, the intended refererence Range("Cell B3:Cell G3") is not proper syntax anyway, AFAIK.


6. With Range(arr(1) & ":" & arr(2)).Interior

That would be instead of Range(arr(1) & ":" & arr(2)).Select followed by With Selection.Interior, as you wrote it.

This is an improvement, not a correction. The point is: there is no need to select the range in order to use it in this context.

Recorded macros often have unnecessary operations. This kind of clean up should be done in order to improve efficiency and conciseness.


Hope that helps.

 

[Luke M]

That was exactly what I needed. Thank you for going above and beyond what I asked to teach me as well. I appreciate it.

 

[Norie]

Luke M

Just curious, why the array?

Couldn't you pass the range reference as a string, eg "B3:G3"?

Also, where are the checkboxes located on the worksheet?

 

[RoryA]

Given that the array is declared outside the routines anyway, there isn't actually a need to pass it as an argument anyway.

 

[Luke M]

Norie,

in retrospect, you are right. I don't need the array. I started this task learning how to adapt my VBA.net from class to excel, and then how to use cell references. From there I tried to pass two cell references to a function, which didn't work. The solution I found in a couple other people's questions was to use an array. And that's my story.

As for the check boxes, they are in the attached image. That is one page of many that all need the same function.

• https://lh3.googleusercontent.com/-...EI/w490-h227-no/Commissioning+Status+Snip.PNG

I just started looking for a way to have the cell references automatically assigned to the subroutine when the check box is selected, but I don't know if that's possible yet. There's so much to learn, it's great

 

[Norie]

Luke

Are you going to have the same Change event code for each checkbox?

 

[Luke M]

Norie,

Yes. I have the same code for each one, with the exception of the cell values. It's a lot of repeated code unfortunately.

Option Explicit


' Array for passing cell values to the function
Dim cbxArray(1 To 2) As String

' Checkbox changed event changes array values to match cells that need a color change
' I manually typed each cell value into each "change event"
Private Sub CheckBox1_Change()
        cbxArray(1) = "B3"
        cbxArray(2) = "G3"
    If CheckBox1.Value = True Then
        Change_Row_Color cbxArray()
    Else
        Remove_Row_Color cbxArray()
    End If
End Sub


Private Sub CheckBox2_Change()
        cbxArray(1) = "B4"
        cbxArray(2) = "G4"
    If CheckBox2.Value = True Then
        Change_Row_Color cbxArray()
    Else
        Remove_Row_Color cbxArray()
   End If
End Sub


Private Sub CheckBox3_Change()
        cbxArray(1) = "B5"
        cbxArray(2) = "G5"
    If CheckBox3.Value = True Then
        Change_Row_Color cbxArray()
    Else
        Remove_Row_Color cbxArray()
   End If
End Sub

' Adds color to rows once checkbox is checked
Public Sub Change_Row_Color(arr() As String)
     With Range(arr(1) & ":" & arr(2)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub


' Removes color from rows when checkbox is unchecked
Public Sub Remove_Row_Color(arr() As String)
    With Range(arr(1) & ":" & arr(2)).Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .ThemeColor = xlThemeColorDark1
         .TintAndShade = 0
         .PatternTintAndShade = 0
     End With
End Sub

 

[Norie]

You could make it slightly easier by using the checkboxes TopLeftCell property.

For example.

Private Sub CheckBox1_Click()
    If CheckBox1.Value Then
        Change_Row_Color CheckBox1.TopLeftCell.Offset(, -5).Resize(, 4)
    Else
        Remove_Row_Color CheckBox1.TopLeftCell.Offset(, -5).Resize(, 4)
    End If
End Sub

Of course you would need to slightly change the subs that do the formatting.

Public Sub Change_Row_Color(rng As Range)
    With rng.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

' Subroutine to remove any row color when the checkbox is deselected
Public Sub Remove_Row_Color(rng As Range)
    With rng.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

PS You could probably streamline things a bit if you were to introduce a class to handle the change event of all the checkboxes.
[/code]