Visual Basic 438 Error When Assigning String to Cell-CodePudding

I copied this text from the "Introduction to Excel VBA Programming" textbook by Guojun Guo:

Sub AccessObject()
Application.ActiveWorkbook.Sheets(1).Cells(1,2).Value = "A String"
End Sub

I get a 438 Error. When I get rid of the Application.ActiveWorkbook.Sheets(1) prefix the code runs. I do not know why this prefix gives the 438 Error. Is this just deprecated or are my settings wrong?

Thanks!

CodePudding user response：

edit: see additional material at the end in response to this comment

It's possible to have sheets in a workbook which aren't worksheets and thus don't have cells. Chart sheets are an example of this. You can check which type of sheet that Sheets(1) is by adding this to your code:

MsgBox Application.ActiveWorkbook.Sheets(1).Type

If the value displayed in the message box is -4167, then Sheets(1) is a worksheet. For anything else, consult the values for xlSheetType

But the simpler solution is to change to using Worksheets(1) instead, because that will only look at worksheets. You can also remove Application.ActiveWorkbook (edit: but see below) because that is the default, which leaves you with:

Worksheets(1).Cells(1, 2).Value = "A String"

edit: As pointed out in this comment, Application.ActiveWorkbook is not the default when the code in question is in a ThisWorkbook module. So, either specify ActiveWorkbook if you want your macro to affect whichever workbook is currently active (there shouldn't be any need to specify Application though):

ActiveWorkbook.Worksheets(1).Cells(1, 2).Value = "A String"

Or, if you want your macro to affect the same workbook that the macro code is in, either specify ThisWorkbook in a normal module:

ThisWorkbook.Worksheets(1).Cells(1, 2).Value = "A String"

Or put the code in the ThisWorkbook module and don't specify anything about the workbook:

Worksheets(1).Cells(1, 2).Value = "A String"

Or, if you want the code to affect a specific workbook that isn't the one which contains the macro and isn't necessarily the active workbook, then capture a reference to that other workbook in a variable and refer to that. A common way to do this is when you open that other workbook:

Dim wb As Workbook
Set wb = Workbooks.Open("filename goes here")

' do stuff

wb.Sheets(1).Cells(1, 2).Value = "A String"

' do other stuff - including potentially saving the workbook

wb.Close
Set wb = Nothing