VBA - Can't use Sheets object without error

I have an Excel file with one sheet called Master.

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).

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
Sep 11 '18 at 19:31

Dim simple as Worksheet

Set simple = worksheets("Master")

Set

Note: Sheetsis an object, not a function.

– Gerold Broser
Sep 11 '18 at 19:34

Sheets

@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
Sep 11 '18 at 20:03

Sheets

Global

Sheets

ActiveWorkbook

@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
Sep 11 '18 at 20:33

@GeroldBroser meh, it's probably just me. I've probably spent way too much time with both hands in VBA's guts. Basically the Excel OM is very keen on naming properties as per the class/type of the object they return (e.g. Worksheet.Range -> returns an Excel.Range). Official documentation happily ignoring the hidden/undocumented modules and making things up is worryingly misleading though. "it's a public property on a hidden undocumented module, so let's just pretend it's a global object, nobody will know".

– Mathieu Guindon
Sep 11 '18 at 20:42

Worksheet.Range

Excel.Range

1 Answer
1

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

Set simple = Sheets("Master")

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

– JesusMonroe
Sep 11 '18 at 19:39

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

– basic_one
Sep 11 '18 at 19:43

@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.

– Mathieu Guindon
Sep 11 '18 at 20:08

Sheets

Worksheet

Chart

Worksheets

Worksheet

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt