Error when set value to an worksheet object

[character]

Hello,

please help on the following issue:

I am trying to set value to an worksheet object by using the following formula which gives me an "Run time rror - subscript out of range":


Set b = Worksheets("""" & Range("A1") & """")


In the Range "A1" of the active sheet i have the Sheet2 - value, and
this "Sheet2" exists on my workbook.

10x

[Opv]

Hello,

please help on the following issue:

I am trying to set value to an worksheet object by using the following formula which gives me an "Run time rror - subscript out of range":


Set b = Worksheets("""" & Range("A1") & """")

In the Range "A1" of the active sheet i have the Sheet2 - value, and
this "Sheet2" exists on my workbook.

10x
Unless I'm missing something, try the following (change sheet name as needed).


Dim b as Range
Set b = Worksheets("SHEETNAME").Range("A1")

[shrivallabha]

Hi Character,

If I understand your attempt correctly then you want to set the object to Range("A1").value.

If yes, then you don't have to worry about the quotes to make it string. The value will be passed as string if you use:
Set b = Worksheets(Range("A1").Value)

[character]

Hello,

thank you both for your answers :)

My first trial which returned an error was:

Set b = Worksheets(Range("A1"))


can you, Shrivallabha, please explain why it is necessary to specify the "Value" property in this situation ? it is not trigerred as default property?

10x

[shrivallabha]

Hello,

thank you both for your answers :)

My first trial which returned an error was:

Set b = Worksheets(Range("A1"))

can you, Shrivallabha, please explain why it is necessary to specify the "Value" property in this situation ? it is not trigerred as default property?

10x
You are partially correct in that it is Range objects default property. It is because the way you code it confuses VBA.

Range("A1") is a valid object [Range] so it uses Range object whereas the syntax requires a string and it throws error Type mismatch [Object Vs String]

Following will work.
Public Sub DefaultUsage()
Dim sValue As String
Dim ws As Worksheet

On Error Resume Next 'Excel will give you error on next line

Set ws = Worksheets(Range("A1")) 'Coding as you tried
If ws Is Nothing Then MsgBox "Fail" Else MsgBox "Success"

sValue = Range("A1") 'Here it loads up its default property.
Set ws = Worksheets(sValue)
If ws Is Nothing Then MsgBox "Fail" Else MsgBox "Success"
End Sub

I hope this helps!

[character]

good to know :)

10x a lot!

[character]

just a small remark:

vba gets confused only if in cell A1 is a string...if, for example, cell A1=2
then it manages to set the correct value to the variable ;)

[Opv]

You are partially correct in that it is Range objects default property. It is because the way you code it confuses VBA.

Range("A1") is a valid object [Range] so it uses Range object whereas the syntax requires a string and it throws error Type mismatch [Object Vs String]

Following will work.
Public Sub DefaultUsage()
Dim sValue As String
Dim ws As Worksheet

On Error Resume Next 'Excel will give you error on next line

Set ws = Worksheets(Range("A1")) 'Coding as you tried
If ws Is Nothing Then MsgBox "Fail" Else MsgBox "Success"

sValue = Range("A1") 'Here it loads up its default property.
Set ws = Worksheets(sValue)
If ws Is Nothing Then MsgBox "Fail" Else MsgBox "Success"
End Sub
I hope this helps!

Boy, was I off base. Sorry about that, Character. In an effort to turn my lack of perception into a learning experience, what would be ones objective in employing the process described? When and how would it be useful?

[shrivallabha]

I just looked at the syntax and I think I spoke little too soon without checking:omg2:

As you start typing, at the following point:
Set b = Worksheets(
intellisense prompts you as below:

_(Index) As Object

So it requires integer as input by default which it doesn't get when we write "Sheet2" in cell A1 but on the contrary when we write 2 it gets integer and works out.

Apart from this default input it accepts explicit string which refers to a valid object name.

[Opv]

I just looked at the syntax and I think I spoke little too soon without checking:omg2:

As you start typing, at the following point:
Set b = Worksheets( intellisense prompts you as below:

_(Index) As Object
So it requires integer as input by default which it doesn't get when we write "Sheet2" in cell A1 but on the contrary when we write 2 it gets integer and works out.

Apart from this default input it accepts explicit string which refers to a valid object name.
Thanks. Forgive me for not being more clear in my question but I was really asking why and when it is useful to set the object to a range value? What does that accomplish and what practical purpose does it serve?

[Opv]

I think you can disregard my last question, unless I am mistaken here. It seems that you are just obtaining the desired Sheet name from the value in a cell rather than hard coding the sheet name when setting the object. I can see how that could be useful.