Basic Worksheet object creation

[liquidroyl]

Hi all -

I am using Excel 2003 and have a very simple (in theory) macro that is supposed to display the name of the active sheet in a message box:

Public Sub DisplayActiveSheetName()
    Dim worksheet1 As Excel.Worksheet
    worksheet1 = ThisWorkbook.ActiveSheet
    MsgBox ("The name of the active sheet is: " & worksheet1.Name)
End Sub

This code resides in a Module.
When running this code, I receive the Run-time error '91' that the

    worksheet1 = ThisWorkbook.ActiveSheet

line "Object Variable or With block variable not set."

Any advice would be appreciated.

Thanks!

[royUK]

You need to use Set like this

Option Explicit

Public Sub DisplayActiveSheetName()
    Dim worksheet1 As Excel.Worksheet
    Set worksheet1 = ThisWorkbook.ActiveSheet
    MsgBox "The name of the active sheet is: " & worksheet1.Name
End Sub

[liquidroyl]

Thanks!

I'm new to VB, coming from a C/C++ background. Unfortunately navigating the msdn for info on Set in VB has been quite fruitless, probably because it's so basic - Can you explain the difference between using Set and a normal assignment? Does the Set command create a reference to the assigned object, as opposed to a copy? If so, why doesn't my previous code work, and just act inefficiently?



Thanks again

[royUK]

Basically you need to use Set with a Worksheet, Workbook or Range object - anything that you can actually write a value into. A String or numeric variable such as Long you don't use it.