CODEWITHSUNDEEP
vbaexcelvariablesnamed-ranges
Simon
Simon

Reputation: 137

A variable of a Named Range (Excel) in a Userform gives me an error

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

Upvotes: 3

Views: 887

Answers (2)

brettdj
brettdj

Reputation: 55682

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

Upvotes: 1

SierraOscar
SierraOscar

Reputation: 17637

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.

Upvotes: 3

Related Questions