- Code: Select all
Private Function FindSheet(ByVal TargetSheet As String) As Long
Dim i As Integer
With ThisWorkbook
For i = .Worksheets.Count To 1 Step -1
If .Worksheets(i).Name = TargetSheet Then Exit For
Next i
End With
FindSheet = i
End Function
The argument this function needs is the name of a worksheet. it will return the index number of that worksheet in the Sheets collection or, if it is not found, 0. It is for this latter effect that the search is conducted backward. As you can address a worksheet either by its name or its number the primary use of this function is to ascertain if the worksheet exists.
The following procedure is called Test(). In fact, it is more like a procedure that would, once the above function is integrated into a project, act as the calling procedure that processes the information provided. That is why it is declared as Public (so that it can be called from Tools / Macro / Macros), and that is also the reason why it is bigger in size than the function that feeds it.
- Code: Select all
Public Sub Test_FindSheet()
Dim Ws As Worksheet
Dim Wn As String ' Worksheet Name
Dim i As Integer
Dim Ttl As String
Wn = ActiveSheet.Name ' substitute with any String
i = FindSheet(Wn)
Ttl = "Sheet index"
If i Then
MsgBox "The sheet was found." & vbCr & _
"Its index is No. " & i, vbInformation, Ttl
' you can insert code here that extracts
' or inserts data from or into the identified sheet
' for example:
Set Ws = ThisWorkbook.Worksheets(i)
Ws.Cells(1, 1).Value = 1122 ' write to A1
Debug.Print Ws.Cells(1, 1) ' read from A1
Else
MsgBox "A sheet by the name of" & vbCr & _
Wn & " exists.", vbCritical, Ttl
' write code here that can handle the event
' that the worksheet isn't available
' f.i create the worksheet
End If
End Sub
Nevertheless, the test procedure also demonstrates how a tertiary function like 'FindSheet' should be tested.
Both procedures should be pasted to a normal code module, such as Module1. If you decide to test this code in your own workbook, note that it will write to A1. To see the result of its reading you need to display the Immediate Window in the VB editor window (Ctl+G).
The variable Wn (Worksheet Name) has been set here to the name of the ActiveSheet. Therefore it is guaranteed to be found. For testing purposes you can set this variable to take any name at all, existing or not. When you integrate the assembly into your project Wn is likely to be determined by your preceding code. You would use this kind of code to extract data from or write data to selected sheets from many. If you wanted similar data from or send the same data to every sheet in a workbook you would probably use a construct that is less selective than this one.