Language-independant way to refer to "Sheet"?

Question

Hello

A friend of mine works as an analyst for an insurance company, and uses Visual Basic for Applications (VBA) to write scripts.

Since her company has offices in different European countries, she needs to make the scripts as language-independent as possible.

One issue that came out recently is how to handle Excel documents and refer to sheets (eg. Sheet1, Sheet2) without having to translate this term in the major European languages.

Does someone know if Excel provides a list of constants that she could use to access different pieces of information regardless of the localized version of Windows/Excel?

Thank you.

Answer 1

You should never use hard-coded names of worksheets to refer to them in VBA.

Dim s As Worksheet

For Each s In ActiveWorkbook.Sheets
  DoSomethingWith s
Next s

or

Set s = ActiveSheet
DoSomethingWith s

or

Set s = ActiveWorkbook.Sheets.Add()
DoSomethingWith s

or

Dim i As Long

With ActiveWorkbook.Sheets
  For i = 1 To .Count
    DoSomethingWith .Item(i)
  Next i
End With

In cell references however, using actual sheet names is inevitable. These references could be built on demand by VBA. Existing references (in cell functions) are kept up to date by Excel automatically.

Answer 2

In the VBA editor, you can rename worksheet objects; this totally separate from the worksheet name displayed on the tab at the bottom of the screen.

I usually rename them something like wksData and wksSummary, and use code like

wksSummary.Range("A1").Value = "abc"

Users can rename the worksheets as they wish, but the VBA reference remains the same.

The name of the worksheet can be retrieved, as in the example below:

MsgBox wksSummary.Name