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

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

Answers


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

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.


Need Your Help

Deduce parameter pack w/out passing it explicitly as a template parameter?

c++ templates parameters c++11 variadic-templates

Forgive any ignorance of C++ type deduction in this, but I'd like to be able to carry around the parameter pack's definition, so that later I could test for an inner type. Is this possible? Somet...

swift : Enum constant with type and value

ios objective-c enums swift ios8

I know, enumeration constant should be like this in swift

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.