apply DIM, SET,"=" to all subs in VBAProject

[heathball]

Set rngS = Selection
Dim LASTRowHK As Long
LASTRowHK = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

is there a way to set the above as applying to all new subs created within the VBAproject

so that if i start a new sub and enter

range(rngS, rngS(LASTRowHK)).Select

it knows what do without including the top 3 lines?

 

[Jeffrey Mahoney]

You can add a line outside any SUBs that looks like this
Public LASTRowHK As Long
Public rngs as Range

As far as the statement "Set rngS = Selection", you'll have to put that in the SUB

 

[Micron]

My guess would be to put those 3 lines in a standard module in the declarations section (at the top, before any code). If your "require variable declaration" option is selected, your first line would be Option Explicit. The lines would go just after that. If not, it should be, but that's just my opinion.
Then you'd need a sub line to reset the active sheet variable every time you select another sheet. If there's no application level or workbook level event for that (I don't know enough Excel vba to know that), then I'd have to think about how to take care of that.

 

[Micron]

Still haven't looked but just thought of another possibility. Your variable could be a function call instead. Every time you use it, the function would return the active sheet (name, or the object itself).

 

[Micron]

Out of curiosity, I tested and can confirm that there is a workbook level event that will fire each time a new sheet is activated. I posted it here in case it helps someone with the same or similar issue in the future. As long as the module level variable is declared as Public (as was suggested) that variable gets a new value every time a different sheet is selected.

In standard module:
Option Explicit
Public strSheetName As String

In ThisWorkbook module (not sheet module):

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   strSheetName = ActiveSheet.Name
   MsgBox strSheetName
End Sub

Select different sheets and message box will return the sheet name. The idea is not to use msgbox function, but to set the variable name and use that as per the original request. I suppose if a procedure activates a certain sheet (rather than just the user) that might be useful as well.