0

I'm new to VBA and am trying to do some basic stuff, but vba doesn't seem to be letting me assign a named range to a variable, and then reference it.

The named range is scoped to the entire workbook, so I shouldn't have to reference the sheet beforehand. Here is the code:

Sub my_function()

    Sheets("TEST").Select
    numRange = Range("num")

    Range(numRange).Select
    Selection.Copy
    Selection.Insert shift:=xlToRight

End Sub

Does anyone know what I'm doing wrong?

MLavoie
  • 9,671
  • 41
  • 36
  • 56
Sean
  • 587
  • 4
  • 20
  • 2
    What is your plan with this? You have no defined variable types. Do you assing values to `numRange` or do you want to assign a range object? The problem right now is that you have not set `numrange` as a range but you try to use it as such. My guess is that there is a value in `num` that would act as an indirect range value? – JvdV Oct 22 '19 at 11:27
  • it was just a simple code to select a named range in a spreadsheet. So yes i think i need to set is as a range – Sean Oct 22 '19 at 11:29

2 Answers2

2

You should Avoid Using Select in general. Try this code instead:

Dim numRange As Range
Set numRange = Sheets("TEST").Range("num")

numRange.Copy
numRange.Offset(, numRange.Columns.Count).Insert xlShiftToRight

You could of course also skip assigning it to a separate range object and use it inside a With block like this:

With Sheets("TEST").Range("num")
    .Copy
    .Offset(, .Columns.Count).Insert xlShiftToRight
End With
riskypenguin
  • 2,139
  • 1
  • 10
  • 22
  • You could of course access it directly, I added that to my answer. I would probably still prefer to assign it to a range object, since I feel it makes it easier to handle. This is debatable of course, and I agree that this use case is simple enough to do it this way. – riskypenguin Oct 22 '19 at 11:48
  • 1
    Looks much cleaner ;) – JvdV Oct 22 '19 at 11:49
1

Without dimming your variables, VBA will try to assign their own. Depending on what your named range Num entitles (single cell or (discontinues) range) the behaviour will differ.

My guess is that you are trying to use an indirect cell reference that sits in num being a single cell. In that case the basic VBA for that would look like:

Dim numRange As Range
With Sheets("TEST")
    Set numRange = .Range(.Range("num"))
End With

So let's say, if num refers to B1, and the value in B1 is a written reference to A1:A5 then numRange will now hold the range object with address: $A$1:$A$5

if my guess is wrong and you just want to have the named range as an range object then there is no need to Set it again, it is allready a range object. Simply refer to it as (for example):

With Sheets("TEST")
    .Range("num").Select
End With

However, you should really avoid the use of .Select

JvdV
  • 70,606
  • 8
  • 39
  • 70