VBA - Can't use Sheets object without error

Question

I have an Excel file with one sheet called Master.

I have a button to delete a value from the database. This is what I have written that's relevant:

Sub delete_this()
    On Error GoTo ErrorCatch
    simple = Sheets("Master")
    .........
ErrorCatch
    MsgBox(Err.Description)
End Sub

It fails immediately when I use Sheets, saying "Application-defined or object-defined error." However I'm using other code that I know works as a reference and they called this no problem (though their file had multiple sheets).

Also in general I'm new to VBA and find it pretty unintuitive with its error messages, and finding out variable values. So any advice there would also be appreciated.

Dim simple as Worksheet - Set simple = worksheets("Master") you forgot the Set — Scott Craner
– Scott Craner, Commented Sep 11, 2018 at 19:31
@GeroldBroser note: in this context, Sheets is a property of the Global hidden module, which returns an object of type Sheets off ActiveWorkbook. So depending on how you look at it, "Sheets" is either a class or a property, but not an object. — Mathieu Guindon
– Mathieu Guindon, Commented Sep 11, 2018 at 20:03
@MathieuGuindon Now I remember why I abandoned VB(A) from my CV a long time ago: a property that's not a container for some value but returns something and which is a class or a property though the doc from the OEM says it's an object. Thanks a lot for reminding me! ;) — Gerold Broser
– Gerold Broser, Commented Sep 11, 2018 at 20:33

Gerold Broser · Accepted Answer · 2018-09-11 19:39:24Z

3

As mentioned in a comment by Scott Craner, you need to use the Set statement whenever assigning an object to a variable when using VBA. This little gotcha doesn't exist in VB.NET, and is easy to forget about these days.

Set simple = Sheets("Master")

edited Sep 11, 2018 at 19:39

Gerold Broser

15.1k6 gold badges56 silver badges124 bronze badges

answered Sep 11, 2018 at 19:34

basic_one

2453 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JesusMonroe Over a year ago

How do I tell which functions simple can call now? For example, I thought simple.Range("value").Value should've worked.

basic_one Over a year ago

have you declared simple with a data type (e.g. Dim simple As WorkSheet)? If you do this, "intellisense" should tell you what methods and properties are available for use on simple when you type the dot after typing simple

Mathieu Guindon Over a year ago

@JesusMonroe Sheets is a collection of many things, including Worksheet objects. It can also include Chart and a half-dozen legacy sheet types. Use the Worksheets collection if you want to be 100% sure you're pulling a Worksheet object.

Collectives™ on Stack Overflow

VBA - Can't use Sheets object without error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related