Microsoft Excel - VBA - pass range as function parameter

Hi guys,

How do I pass a range to a function as a parameter?

Let's say I have the function

Function MyFunction(myRange as Range)

 'Do something with myRange...

End Function

I tried as follows by it doesn't compile.

MyFunction( Sheets(DataSheet).Range("B5:C9") )

MyFunction( "DataSheet!$B$5:$C$9" )

Thanks in advance for any help.

Aldo.

try this one

dim objSheetRange as Range
objSheetRange = Sheets(DataSheet).Range("B5:C9")
MyFunction( objSheetRange )

Hi Harry,

I tried that, but getting run-time error 91 on the line below:

  objSheetRange = Sheets(DataSheet).Range("B5:C9")

Hi,

objSheetRange = Sheets(DataSheet).Range("B5:C9")

DataSheet is this a variable or sheet name?

if sheetname then it should be written like

objSheetRange = Sheets("DataSheet").Range("B5:C9")

and have included reference of excel in your application?

but still doesn't work..

I tried the following VERY simple code and it works just fine. It seems that you need to CALL the function. When I simply go with CopyTest(Range("A5") I also get an error.

Have fun.

Sub Test()
    Call CopyTest(Range("A5"))
End Sub
Sub CopyTest(r As Range)
    r.Value = "Test2"
End Sub

end of post

I gave a similar problem, I wish to hide a collection of rows based on a value in a range.  This can be achieved by a loop that hides them one by one. thios i want ot avoid because it is very slow when that range is linked to word as it triest update the ling in eacht step.  thus i want the sub to collect all rows, and them hide them all at once. The sub below works fine when I code the range I want to evaulate directy in the code.  I need to repeat this function for different ranges and thus would like call the function while simply passing the range instead of hard coding it. But this doesnt work :(  :(

This for example works fine


 call hiderows(hidevalue)
where the range is hardcoded.

This I cant get to work:
call hiderows(hidevalue,namedrange as Range)


Sub hiderows(RNG As range, hidevalue As Variant)
 
 
  Dim needToShow As range, needToHide As range
 
  Dim cell As range
  For Each cell In RNG

    If cell.Value = hidevalue And (cell.EntireRow.Hidden = False) Then
    If needToHide Is Nothing Then
      Set needToHide = cell
    Else
      Set needToHide = Union(needToHide, cell)
    End If
    End If

    If Not cell.Value = hidevalue And (cell.EntireRow.Hidden = True) Then
    If needToShow Is Nothing Then
      Set needToShow = cell
    Else
      Set needToShow = Union(needToShow, cell)
    End If
    End If
Next

    
If Not needToHide Is Nothing Then needToHide.EntireRow.Hidden = True
If Not needToShow Is Nothing Then needToShow.EntireRow.Hidden = False

End Sub

Solution:


It works when the range is passed as follows:


call hiderows(Range("A1:A20"),0)

now ill try named ranges

Just wanted to say thanks.
I've been struggling with ranges in function calls for a very long time, and it just never occured to me that you actually have to send it as a range object. I always tried to send it as a string;
tmpVar = TempFunction("A1")

No wonder it never worked for me. :)

Thanks. I tried it and it worked. When passing a range to a function it worked when I added a "Call" before the function name!