VBA: Using worksheet name and/or range name as inputs to function

Question

I have a simple function that shades every other row of a given range in a worksheet:

Public Function ShadeEveryOtherRow()
Sheets("mySheet").Select
ShadedRows = Range("myRange").Rows.Count
' determines the number of rows to shade in the range

' Code that loops through and shades rows here

End Function

I would like to be able to call this function with a given worksheet name and range as inputs. If I write the function as shown below and attempt to execute using the test() sub, I get a "Compile Error: Type mismatch" error. Any assistance would be appreciated. Thanks.

Public Function ShadeEveryOtherRow(targetSheet As Worksheet, targetRange As Range)

Dim targetSheet As Worksheet
Dim targetRange As Range

Sheets(targetSheet).Select
shadeRows = Range(targetRange).Rows.Count

'Code that shades rows here

End Function


Sub test()
ShadeEveryOtherRow "mySheet", "myRange"

End Sub

Answer 1

targetRange and targetSheet should be of type string, instead of being passed to the function as objects.

Public Function ShadeEveryOtherRow(targetSheet As string, targetRange As string)

Dim targetSheet As Worksheet
Dim targetRange As Range

Sheets(targetSheet).Select
shadeRows = Range(targetRange).Rows.Count

'Code that shades rows here

End Function


Sub test()
ShadeEveryOtherRow "mySheet", "myRange"

End Sub

Answer 2

Don't Dim your variables twice. Declaring them in the function signature is sufficient. Also, don't use .Select. Do what you want to do explicitly.

Public Function ShadeEveryOtherRow(targetSheet As string, targetRange As string)

shadeRows = Sheets(targetSheet).Range(targetRange).Rows.Count

'Code that shades rows here

End Function

Edit: Like Sigil points out below you should be using strings for your variables. THe other option is to pass in the range explicitly.

Public Function ShadeEveryOtherRow(targetRange As Range)

    shadeRows = targetRange.Rows.Count

    'Code that shades rows here
End Function


Sub test()
    ShadeEveryOtherRow Sheets("mySheet").Range("myRange")
End Sub

And remember! Always use Option Explicit at the top of all your code.