Method Range of Object Global failed

May 2, 2012 3:49 AM

I'm starting to write some Excel-VBA Macros, but it seems I don't get some basic things. I tried this simple code:

Sub Macro1()

Dim MyRange As Range

Set MyRange = Worksheets("Sheet1").Range("A1")
MsgBox (MyRange)
'MyRange.Copy
Range("MyRange").Copy

Range("A2").Select
ActiveSheet.Paste

End Sub

It displays the content of the cell and copies it with MyRange.Copy, however if I use Range("MyRange").Copy instead I get a Method Range of Object Global failed. Why is this so?

posted by SweetLiesOfBokonon to computers & internet (4 answers total)

"MyRange" as used in Range("MyRange").Copy is being passed as a string, so VBA is looking for a range with the *name* MyRange rather than your MyRange variable.

On the line below it: "A2" is being passed as a string (of cells names). Note that you didn't need to declare an "A2" range beforehand to use it.
posted by unixrat at 4:54 AM on May 2, 2012

Yeah, ranges are a PIT to work with, at first. Ideas like:
MsgBox (MyRange)
are doomed to failure. Instead, the best you can do is output the first cell location, width, and height. The easiest debugging way to validate your range code is to activate the range (equivalent to selecting it with your mouse)... but this only works if the sheet it's on is active, too! Naturally, you'll need to pause your code at this point, or the selection (active cells) may be changed by later code.
posted by IAmBroom at 8:47 AM on May 2, 2012

BTW, long ago I wrote an Excel function that would display a very descriptive msgbox about any given range: Size, location, values of all four corner cells (if multicellular).

Memail me if you would like the code.
posted by IAmBroom at 8:56 AM on May 2, 2012

When you specify something in quotes using Range(), VBA will look for a match in all of the names that have been specified within Excel but not the name of range variables defined in VBA. So, VBA will look through

• all of the default names ("A1", "A2", ... , "B1", "B2", etc)
• the user-defined Named Ranges

If there isn't a match there then you will get the "Method Range of Object Global failed" error.

I've broken that down because you may have seen examples with things like Range("ABigLongName").Copy and thought that they were doing what you have tried i.e. using the name of a range variable previously declared in the VBA. In fact, these will be referring to a Named Range as mentioned above.
posted by MUD at 9:09 AM on May 2, 2012