Excel VBA: Passing a range name as a function argument

Question

I'm writing a macro to do a complex copy/paste exercise. It's pretty straightforward conceptually, but I'm stuck in one spot. All of the various blocks of data are identified with various named ranges. I need iterate through this list of names, passing each name as an argument to a function (actually a subroutine, but same idea). The source of the data is in one workbook while the destination is in another workbook.

Here is what I have (for just one block of data):

Private Sub copyABU()
   copyPaste(ThisWorkbook.Names("myRange1").RefersToRange)
   copyPaste(ThisWorkbook.Names("myRange2").RefersToRange)
   copyPaste(ThisWorkbook.Names("myRange3").RefersToRange)
   //etc
End Sub

Private Sub copyPaste(thisRange As Range)
    Windows(someworkbook).Range(thisRange).Copy
    Range(thisRange).PasteSpecial Paste:=xlPasteValues
End Sub

Unfortunately, I get a run-time error on this. I think that there is a type mismatch, but I'm not sure about this and can't figure out what I am missing. Can anyone see why this fails? (I'm using Excel 2010).

Thanks!

systrue · Answer 1 · 2012-05-24T02:31:01.687

1

here my answer:

Sub init()
Windows("Book1.xlsx").Activate
Call copyPaste(2, Range(Cells(1, 1), Cells(10, 10)), "copy")
Windows("Book2.xlsx").Activate
Call copyPaste(1, Range(Cells(1, 1), Cells(10, 10)), "paste")
End Sub

Function copyPaste(wksInt As Integer, thisRange As Range, copyPasteStr As String)
    Dim workSheetRange As Range
    With Worksheets(wksInt)
        Set workSheetRange = thisRange
    End With
    workSheetRange.Select
    If copyPasteStr = "copy" Then
        Selection.Copy
    Else
        Worksheets(wksInt).Paste
    End If
End Function

This works as long as the Ranges for copy and paste are the same dimension. If the dimension varies, you need to change workSheetRange.Select into the condition and only select the first cell of the range for pasting. PAX

edited May 24 '12 at 02:31

answered May 24 '12 at 00:07

systrue

89
8

Thanks. I neglected to mention 2 things: First, I have only a single source file and a single destination file, so the names of the workbooks are easy. Second, the range name are identical in both files. So the only argument needed to be passed to the function is the name of the range that I am dealing with at that particular moment. If I can accomplish that, the rest should be easy. Even with your suggestion, I still have the same problem, which is that I can't get the argument to pass into the callee function, even if all I did was a "Hello World" message box inside of it. – AndroidDev May 24 '12 at 00:18
I think that I got it. Rather than try to pass the range as an object, I just passed the name of the range as a string and then went on from there. Seems to work just fine. Thanks for the help! I appreciate it. – AndroidDev May 24 '12 at 00:43
maybe you've seen my updated code above, so if this helped you, you may could close the case. THX PS: if there is any more question about using different ranges, just let me know! – systrue May 24 '12 at 05:26

score 1 · Answer 2 · edited May 23 '17 at 10:34

Your code will work with a couple small tweaks.

First, you need to prefix your call to copyPaste with the word Call. (See note below if you don't want to.)

Private Sub copyABU()
   Call copyPaste(ThisWorkbook.Names("myRange1").RefersToRange)
   Call copyPaste(ThisWorkbook.Names("myRange2").RefersToRange)
   Call copyPaste(ThisWorkbook.Names("myRange3").RefersToRange)
   ' //etc
End Sub

Second, add a .Address after thisRange.

Private Sub copyPaste(thisRange As Range)
    Range(thisRange.Address).Copy
    thisRange.PasteSpecial Paste:=xlPasteValues
End Sub

I didn't want to bother with creating a someworkbook variable, so I just deleted that part.

Note: You have to use the Call keyword if you are calling a procedure with an argument list enclosed in parentheses. https://stackoverflow.com/a/7715070/138938

If you don't want to use the Call keyword, omit the parens:

copyPaste ThisWorkbook.Names("myRange1").RefersToRange

Excel VBA: Passing a range name as a function argument

2 Answers2