How can I convert a string variable to a worksheet object on VBA?

Question

My problem is complex, I do not know if somebody has had this problem. I want to convert a string ("Sheet1", "Sheet2", etc.) to an object in a particular case in a worksheet. With that, I can use a code Sheet3.Range("A1").Value, but I want to replace the number 3 with any I need.

I have a workbook with a worksheet called "CSV", in this sheet has a table called "t_csv". This worksheet is used to translate some text between Spanish and English.

CSV sheet example

I programed the routine to do that as follows (it works if you have the same order on the tab bar and VBA Index).

Sub tranlation()
Dim lang As String
Dim Sheet As Integer
Dim vcell As String
Dim data As String
‘Get the language from a buttom btn_esp or btn_eng  from userform called “f_Login”
  If f_Login.btn_esp.Value = True Then lang = "Español"
  If f_Login.btn_eng.Value = True Then lang = "English"
‘Bucle to get the data from table “t_csv” located in sheet “CSV” (Sheet2)
  For i = 1 To Sheet2.ListObjects("t_csv").DataBodyRange.Rows.Count
     With Sheet2.ListObjects("t_csv")
          Sheet = .ListColumns("Hoja").DataBodyRange(i).Value
          vcell = .ListColumns("Celda").DataBodyRange(i).Value
          'It uses the lang variable to choose the respective column on table "t_csv"
          data = .ListColumns(lang).DataBodyRange(i).Value
          ‘write the text in corresnponding cell (code to be changed)
          **Sheets(Sheet).Range(vcell).Value = dato**
     End With
  Next i
End Sub

The problem with the function Sheets() or Worksheets() is that they call the worksheets numbering the worksheets on the workbook's tab bar (from left to right). But I want to call the sheets with the VBA worksheet number because I have specific order on the workbook's tab bar.

Order in my tab bar

Order in my VBA index

I tried to use a code like this (it does not work because "Sheet" & Sheet is a string variable):

Dim SheetIndex As Worksheet
     Set SheetIndex = “Sheet” & Sheet
     SheetIndex.Range(vcell).value = data

But if I use the next code:

Dim HojaIndex As Worksheet
     Set HojaIndex = Sheet3
     HojaIndex.Range(vcell).value = data

This code works, but I want to substitute the number 3 automatically by whatever number I get from the table "t_csv".

I am guessing that the solution is to use the function CallByName, but I don't understand how can I use it. I saw some examples and I tried, but I could not make it work.

------- UPDATE ------- UPDATE ------- UPDATE ------- UPDATE ------- UPDATE ------- UPDATE -------

Thanks to @Tim Williams. I could solve my problem, I did some modifications inspired in his answer. I share the final answer if somebody has the same problem.

Function SheetByCodeName(SheetCodeName As String) As Worksheet
Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
    If ws.CodeName = SheetCodeName Then
      Set SheetByCodeName = ws
      Exit Function
    End If
  Next ws
End Function

Sub tranlation()
Dim lang As String
Dim Sheet As String    'Update variable type from Integer to String
Dim vcell As String
Dim data As String
Dim SheetName As Worksheet    'New variable to set the sheet name
‘Get the language from a buttom btn_esp or btn_eng  from userform called “f_Login”
  If f_Login.btn_esp.Value = True Then lang = "Español"
  If f_Login.btn_eng.Value = True Then lang = "English"
‘Bucle to get the data from table “t_csv” located in sheet “CSV” (Sheet2)
  For i = 1 To Sheet2.ListObjects("t_csv").DataBodyRange.Rows.Count
     With Sheet2.ListObjects("t_csv")
          Sheet = "Sheet" & .ListColumns("Hoja").DataBodyRange(i).Value
          vcell = .ListColumns("Celda").DataBodyRange(i).Value
          'It uses the lang variable to choose the respective column on table "t_csv"
          data = .ListColumns(lang).DataBodyRange(i).Value
          ‘*-NEW CODE*- write the text in corresnponding cell
          Set Sheet = SheetByCodeName(Sheet)
          Sheet.Range(vcell).Value = data
          ‘*-NEW CODE*- write the text in corresnponding cell
     End With
  Next i
End Sub

Something like the loop in the answer posted [here](https://stackoverflow.com/questions/41477794/refer-to-sheet-using-codename). — BigBen, Jul 01 '20 at 19:11

score 0 · Accepted Answer · answered Jul 01 '20 at 19:43

Sub tester()

    Debug.Print SheetByCodename("xxx").Name

End Sub


'Return a worksheet from its codeName (or Nothing if not match)
'  Defaults to ThisWorkbook if a workbook is not passed
Function SheetByCodename(nm As String, Optional wb As Workbook = Nothing) As Worksheet
    Dim ws As Object
    If wb Is Nothing Then Set wb = ThisWorkbook
    For Each ws In ThisWorkbook.Worksheets
        If ws.CodeName = nm Then
            Set SheetByCodename = ws
            Exit Function
        End If
    Next ws
End Function

Thanks a lot @Tim Williams. Your code was useful to me. I did some modifications inspired in your answer. I only have one workbook, so I did a little simplification. I update my original post to get the final solution to my problem. — Ghost, Jul 02 '20 at 21:44

How can I convert a string variable to a worksheet object on VBA?

1 Answers1