Tags:

Method Range of Object Global failed
May 2, 2012 3:49 AM   Subscribe

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) 3 users marked this as a favorite
 
"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 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


« Older Besides the Wikipedia listings...   |  Power steering failure, AA guy... Newer »
This thread is closed to new comments.