VBA Compile error, how to solve?

[Janb]

Excel 2016 / file is on an external drive (NAS) / using Option Explicit

Hi all,

Yesterday I wrote a (imo) simple vba script and it was working. Another VBA script with the same DIM and Set to my surprise didn't work.

Today none of them are working. The error is: Compile error: Expected variable or procedure, not module.

First the one who worked perfectly yesterday but not anymore

Sub WorkbookTasks()

Dim WK As Workbook
Dim WS As Worksheet

'"Y:\001 Office\01 Excel\Zelfstudie\VBA\# Eigen Oefeningen\Workbooks ExcelMacroMastery.xlsm")

Set WK = Workbooks("Workbooks ExcelMacroMastery.xlsm")


    For Each WK In Application.Workbooks
        For Each WS In WK.Worksheets
    
    Debug.Print WK.Name, WS.Name 'Just the name of the file
    
    Debug.Print WK.FullName, WS.Name 'Including the directory
    
        Next WS
            Next WK
End Sub

The one who didn't work yesterday and today (same DIM and Set):

Sub WorkbookSave()

Dim WK As Workbook

Set WK = Workbooks("Workbooks ExcelMacroMastery.xlsm")

WK.Save


End Sub

Thanks in advance for your help.

Cheers Jan

 

[Jaafar Tribak]

Which line is highlighted by the compiler ?

 

[Janb]

Which line is highlighted by the compiler ?

After pressing F5, the word Workbooks is blue (Set WK = Workbooks("workbooks.....xlsm")

After pressing OK after the error message, Sub WorkbookSave() becomes yellow.

 

[Janb]

Strange issue. I just wrote the same scripts in a new workbook with a new name and they are all working. Still don't understand the problem in the other workbook (Workbooks ExcelMacroMastery.xlsm)

Sub PrintWBnames()

Dim WK As Workbook
Dim WS As Worksheet

Set WK = Workbooks("Workbooks Test Macros.xlsm")

For Each WK In Workbooks
    For Each WS In WK.Worksheets

Debug.Print WK.Name, WS.Name

    Next WS
        Next WK
        

End Sub

 

[Jaafar Tribak]

Do you have a module in your project that has the name : Workbooks ? If so, then change its name

 

[Janb]

Do you have a module in your project that has the name : Workbooks ? If so, then change its name

Dear Jaafar,

Thanks a lot. That was the problem. Now the comment in the error (....not module) makes sense.

Cheers Jan

 

[Jaafar Tribak]

Glad it is now working for you