VBA error 424 "Object Required" for Excel vlookup in Word Macro-CodePudding

I'm trying to do an MS Excel vlookup on an MS Excel workbook from a MS Word macro.

My MS Word macro:

Sub Salary_Step1()

bool_debug = True ' set to False once code is working

Dim int_PayGrade As Integer
Dim xlApp, xlWorkBook As Object
Dim str_Locality As String, str_WorkBook As String, str_Worksheet As String

Set xlApp = CreateObject("Excel.Application")

int_PayGrade = ActiveDocument.FormFields("PayGrade").DropDown.Value - 1
str_Locality = ActiveDocument.FormFields("Locality").Result

If (bool_debug) Then MsgBox ("PayGrade = " & int_PayGrade & vbCrLf & "Locality = " & str_Locality)

Application.ScreenUpdating = False

str_WorkBook = "C:\Users\" & Environ("Username") & "\Documents\Announcement Postion Template\PayScales.xlsx"
str_Worksheet = ActiveDocument.FormFields("Locality").Result
If Dir(str_WorkBook) = "" Then
    MsgBox "Cannot find the designated workbook: " & str_WorkBook, vbExclamation
    Exit Sub
End If

With xlApp
    .Visible = True ' set to False when code is working
    Set xlWorkBook = .Workbooks.Open(FileName:=str_WorkBook, ReadOnly:=True, AddToMru:=False)
    With xlWorkBook
        Set xlSheet = .Worksheets(str_Worksheet)
        With xlSheet
            .Select ' comment out when code is working
            int_salary_step1 = .WorksheetFunction.VLookup(int_PayGrade, Sheet1.Range("A1:K16"), 2, False)
            int_salary_step10 = .WorksheetFunction.VLookup(int_PayGrade, Sheet1.Range("A1:K16"), 11, False)
        End With
        .Close False ' set to True when code is working
    End With
    .Quit
End With

If (bool_debug) Then MsgBox ("Pay Step 1 = " & int_salary_step1 & vbCrLf & "Pay Step 10 = " & int_salary_step10)

Set xlWorkBook = Nothing: Set xlApp = Nothing
Application.ScreenUpdating = True

End Sub

I know that the workbook is being opened, because of the .Visible = True ' set to False when code is working in the first With and the correct sheet is being selected because of the .Select ' comment out when code is working in the third With.

But I'm getting an Error 404 Object Required when I try to execute either of:

int_salary_step1 = .WorksheetFunction.VLookup(int_PayGrade, Sheet1.Range("A1:K16"), 2, False)
int_salary_step10 = .WorksheetFunction.VLookup(int_PayGrade, Sheet1.Range("A1:K16"), 11, False)

I've Dimed xlApp, xlWorkBook and xlSheet as Objects, and Set each one, so I really think I'm working with an Object when I try to .WorksheetFunction.VLookup(...).

Can someone lend a brother a hand?

Thanks!

CodePudding user response：

As per the comments from BigBen and Timothy Rylatt, I added Option Explicit, discovered that I hadn't Dimed all of my variables, and in particular sheet1, which was a typo, it should have been xlSheet. So my bad.

But having Dimed all of my variables, I got a

Run-time error '438'
Object doesn't support this property or method

For no good reason really, I tried

            int_salary_step1 = .Application.VLookup(int_PayGrade, Sheet1.Range("A1:K16"), 2, False)
            int_salary_step10 = .Application.VLookup(int_PayGrade, Sheet1.Range("A1:K16"), 11, False)

as opposed to

            int_salary_step1 = .WorksheetFunction.VLookup(int_PayGrade, Sheet1.Range("A1:K16"), 2, False)
            int_salary_step10 = .WorksheetFunction.VLookup(int_PayGrade, Sheet1.Range("A1:K16"), 11, False)

and, Viola!, it runs properly!

So thanks BigBen and Timothy Rylatt!