Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Compile Error In Vba - In Valid Use Of Me Keyword

Posted by: bakersburg9 Aug 7 2019, 09:52 AM

Can't understand why I'm getting this message - I want to rename the worksheets with the value in A1 - actually, I don't really need it to do all the worksheets, like my code is written for - I just really need it to do the CURRENT worksheet - but I'm just testing it out...

CODE
Sub goToNextWorksheetTEST()
    
Dim WsheetTEST As Worksheet
         On Error Resume Next
             For Each WsheetTEST In Me.Worksheets
             WsheetTEST.Name = WsheetTEST.Range("A1")
             Next WsheetTEST
         On Error GoTo 0
        
  End Sub

Posted by: theDBguy Aug 7 2019, 10:13 AM

Hi. I don't code in Excel but does it use This in place of Me? Just wondering...

Posted by: DanielPineault Aug 7 2019, 11:13 AM

Me. is an Access thing.

For Excel you would use Application.Worksheets

CODE
Sub goToNextWorksheetTEST()
    Dim WsheetTEST            As Worksheet

    On Error Resume Next
    For Each WsheetTEST In Application.Worksheets
        WsheetTEST.Name = WsheetTEST.Range("A1")
    Next WsheetTEST
    On Error GoTo 0
End Sub

Posted by: cheekybuddha Aug 7 2019, 11:23 AM

You can use Me in Excel if the function/sub is declared in the ThisWorkbook module.

Otherwise you will have to set an explicit reference to the workbook/worksheet or use something like the hideous ActiveWorkbook/ActiveSheet.

hth,

d

Posted by: bakersburg9 Aug 7 2019, 12:38 PM

Thanks, guys! How would I edit it to just run for the active workbook, then go to the next workbook ? I know that at the end of the day, that's what it happens to be doing, but I want this to run at the end of a block of code - then perform the same process, and THENdo the saving with the worksheet name as the value in cell A1

Thanks so much !!! cool.gif

Steve

Posted by: ipisors12 Aug 7 2019, 01:19 PM

The proper way to code this is neither Me nor Application ... but rather, ThisWorkbook.Worksheets (or x.Worksheets, where x is the name of a propertly typed and set workbook variable).

Posted by: DanielPineault Aug 7 2019, 01:36 PM

ipisors12 is entirely right (ThisWorkbook.Worksheets). 2 weeks off and I'm already rusty. crazy.gif

Posted by: cheekybuddha Aug 7 2019, 04:29 PM

However, when you are within the ThisWorkbook module you can use Me, just as when you are in Access you do not refer to Forms.myForm when you are within myForm's module. shrug.gif

@Isaac - long time no see! wavehi.gif

d

Posted by: ipisors12 Aug 7 2019, 06:02 PM

David - indeed! Good to see you hope all is well thumbup.gif

Posted by: Debaser Aug 8 2019, 07:01 AM

Not just ThisWorkbook - it works in any class module to refer to the current instance of that class.

Posted by: cheekybuddha Aug 8 2019, 07:48 AM

>> it works in any class module to refer to the current instance of that class. <<

Very true - great point! thumbup.gif

Yet in the context of the code in the original post, it will either be ThisWorkbook or a class that exposes a Worksheets property returning a Collection of Worksheets or objects with some similar properties! grin.gif

d

Posted by: Debaser Aug 9 2019, 07:42 AM

Yep, totally agree. smile.gif