Using active sheet instead of using sheet name Public / Private Sub code-CodePudding

I want to use the code below without having to write out the sheet name in Sheets("Sheet1").Activate in the Public Sub function, so I can just click on the sheet and run the code. I am unfamiliar with VBA language, and this should be a simple fix, but after an hour of trying things out, I can't get it working so I'm asking here now. Also, when I remove the Sheets("Sheet1").Activate, the abbrev sheet is then activated so the function does not run on that instead of my desired sheet. Any help would be appreciated!

Public gcolWords As New Collection

Public Sub ReplaceAllWrds()
Dim vWord, vAbv, itm
Dim i As Integer
Dim Lastrow As Integer

LoadAbbrevs

Sheets("Sheet1").Activate
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("F" & Lastrow).Select
For Each itm In gcolWords
   i = InStr(itm, ":")
   vWord = Left(itm, i - 1)
   vAbv = Mid(itm, i   1)
   Replace1Wrd vWord, vAbv
Next
Set gcolWords = Nothing
End Sub

Private Sub Replace1Wrd(ByVal pvWrd, pvAbv)
On Error Resume Next


    Selection.Replace What:=pvWrd, Replacement:=pvAbv, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Private Sub LoadAbbrevs()
Dim vWord, vAbv, vItm

Sheets("abbrevs").Activate
Range("A2").Select
While ActiveCell.Value <> ""
   vWord = ActiveCell.Offset(0, 0).Value
   vAbv = ActiveCell.Offset(0, 1).Value
   vItm = vWord & ":" & vAbv
   
   gcolWords.Add vItm
   ActiveCell.Offset(1, 0).Select   'next row
Wend
End Sub

CodePudding user response：

You should define a Excel.Worksheet that points to the ActiveSheet, and then use it to keep the reference:

Public Sub ReplaceAllWrds()
    Dim vWord, vAbv, itm
    Dim i As Integer
    Dim Lastrow As Integer
    Dim ws As Excel.Worksheet
    
    Set ws = ActiveSheet
    
    LoadAbbrevs

    ws.Activate
    'MsgBox "The name of the ws sheet is " & ws.Name
    Lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    Range("F" & Lastrow).Select
    For Each itm In gcolWords
       i = InStr(itm, ":")
       vWord = Left(itm, i - 1)
       vAbv = Mid(itm, i   1)
       Replace1Wrd vWord, vAbv
    Next
    Set gcolWords = Nothing
End Sub

CodePudding user response：

You do not need to activate the sheet if you're using worksheet objects.

Dim ws As Excel.Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Cells(1,1).Value = "Example"

Using the worksheet objects can speed up your code and you don't need to worry about what sheet is active.

The following is a quick example of what your LoadAbbrevs could be.

Sub Example()
Dim WS as Worksheet
Set WS = ThisWorkbook.Sheets("abbrevs")
Dim RowIndex as Integer
RowIndex = 2
While WS.Cells(RowIndex, 2).Value <> ""
    gcolWords.add WS.Cells(RowIndex, 2).Value   ":"   
    WS.Cells(RowIndex, 3).Value
    RowIndex = RowIndex   1
Wend
End Sub