3

I am very new to Excel-VBA (any kind of programming actually) and I am looking for help.

I am trying to make a variable of a Named Range in a Userform (Excel-VBA), but when I try to use the macro with the created variable I receive an error:

Run-time error '1004': Method 'Range' of object '_Worksheet' failed

How to reproduce:

1) Make a Userform with a label (named test1 in my code).

2) Make Module for the macro.

Here is the code for the Userform and the Module.

The Userform

Private Sub UserForm_Initialize()

Dim wsTest As Worksheet
Dim rnTest As Range

Set wsTest = Sheets("Test")
MsgBox wsTest.Name

Set rnTest = wsTest.Range("NamedRange")
MsgBox rnTest.Name

Me.Test1.Caption = Format(wsTest.Range("rnTest")(1).Value, "$#,##0")

End Sub

And the Module

Sub NR_Test()

UserForm1.Show False

End Sub

The variable 'wsTest' works. I know this because when I enter the Named Range-name that I see in the Name Manager (NamedRange), the Macro works.

I have searched Stackoverflow and other resources to look for a solution. Most posts tell me I need to use "Set" to make a variable from a range, but unfortunately that is not enough. I am missing some vital piece and I can't put my finger on it.

Any help would be very much appreciated. If there are any question, please don't hesitate to ask.

Cheers, Simon

Simon
  • 137
  • 2
  • 8

2 Answers2

3

Change

Format(wsTest.Range("rnTest")(1).Value, "$#,##0")

To

Format(rnTest(1).Value, "$#,##0")

rnTest is the name of the range object you are targeting, but when you pass it in quotation marks within the Range() method it is treated as a literal string. Unless you also have a named range called "rnTest" this will always fail.

As you can see from the above example, you can now access the rnTest object directly, as the reference has been Set earlier. Hope that makes sense.

SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • This was the answer I was looking for! If I understand you correctly I was not calling upon the new variable at all. I was talking directly to a sheet and was asking for a Named Range that was not there. This concludes a nice first quest :) – Simon Mar 30 '16 at 19:25
  • That's exactly what I was explaining - glad you understood the concept, shouldn't take you long to pick up VBA at all. – SierraOscar Mar 30 '16 at 19:28
1

You should able to cut this code down to just this presuming the range name is global, i.e. you don't need to work with the worksheet.

Private Sub UserForm_Initialize()
Dim rnTest As Range
Set rnTest = Range("NamedRange")
Me.Test1.Caption = Format(rnTest.Cells(1).Value, "$#,##0")
End Sub
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • Or even `Test1.Caption = Format([NamedRange].Cells(1), "$#,##0")` – SierraOscar Mar 30 '16 at 13:22
  • I cut out the variable to point to the worksheet. For now I am working with global Named Ranges. I did not even know you were able to make localised Named Ranges specific to sheets. Thanks for the helpfull tip. – Simon Mar 30 '16 at 19:28