CODEWITHSUNDEEP
vbaexcel
otus
otus

Reputation: 385

Multi language Excel VBA Application

I basically created an Excel VBA application that manipulate Excel worksheets, so in the code, I use the string "Sheet1" to refer to the first sheet of a workbook, but when I try to use this application with the same code with a french version of Excel, it doesn't work until I translate "Sheet1" to "Feuil1". So my question is, is there a way to automatically adapt the code to any version of Excel ?

Upvotes: 0

Views: 2241

Answers (2)

Ralph
Ralph

Reputation: 9434

The only possible way I can think of to always reference "sheet1" in the local language is the following code.

Option Explicit

Public Sub GetLocalNameForNewSheets()
Dim strSheetName As String
Dim i As Long

i = ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets.Add After:=Worksheets(i)
strSheetName = ActiveWorkbook.Worksheets(i + 1).Name
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets(i + 1).Delete
Application.DisplayAlerts = True

Debug.Print strSheetName
For i = 1 To Len(strSheetName)
    While IsNumeric(Mid(strSheetName, i, 1))
        strSheetName = Replace(strSheetName, Mid(strSheetName, i, 1), "")
    Wend
Next i
Debug.Print strSheetName
Debug.Print strSheetName & "1"

End Sub

Basically, I am asking Excel to create a new sheet and name it for me. Then, I am getting the new name which is "sheet" in the local language and remove from the string the number part. At the end, you can add the number "1" to reference the first sheet.

Upvotes: 1

R.Katnaan
R.Katnaan

Reputation: 2526

You can use the following ways to get a sheet from code:

(1) using by Sheets(sheet_index)

This way cannot be adapt because it take the sheet by sheet index (sheet index are start from 1). When sheet are change place, it cannot access the right sheet.So, it should not use.

For example: Set Feuil1Sheet = Sheets(1)

(2) using by (Name) of VBA editor

I think this way should not use never, because it takes the sheet by code name which can only visible by VBA editor(it shows as (Name) field in sheet properties). I think you are using this way for getting the first sheet. So, you not get the right sheet. One thing you need to know is that code name of every first sheet may not be Sheet1 always. It can be Sheet2 or Sheet4, etc.

For example: Set Feuil1Sheet = Sheet1

(3) using Worksheets("sheet-name") or Sheets("sheet-name")

This last way is a very compatible way and can be adapt in anywhere Excel because it take the sheet by its name. So, If names are equal, you will get the right sheet. So, use this for getting the sheet.

For example: Set Feuil1Sheet = Worksheets("Feuil1") or Set Feuil1Sheet = Sheets("Feuil1")

Upvotes: 2

Related Questions