CODEWITHSUNDEEP
excelvbainternationalization
ExcelIsFun_Sometimes
ExcelIsFun_Sometimes

Reputation: 45

How to change spreadsheet to US (ThisWorkbook) from German (DieseArbeitsmappe)?

Our company has a lot of workbooks that were originally saved in German. Therefore, there is no ThisWorkbook variable. Instead it's called DieseArbeitsmappe. Using ThisWorkbook in code results in VBA an error.

I tried renaming DieseArbeitsmappe to ThisWorkbook with the code below.

Private Sub RenameThisWorkbookToEnglish()
  Dim TmpWorkbook As Object

  On Error Resume Next
  'Was this saved in German?
  Set TmpWorkbook = ActiveWorkbook.VBProject.VBComponents("DieseArbeitsmappe")

  If err.Number = 0 Then
    Debug.Print ("German Workbook.")
    TmpWorkbook.Module.CodeName = "ThisWorkbook"
    TmpWorkbook.Name = "ThisWorkBook"
    TmpWorkbook.CodeName = "This Workbook"
    Exit Sub
  End If

  On Error GoTo -1
End Sub

Function ErrorIsThisWorkBookBad() As Boolean
  On Error GoTo ErrLabel
  ErrorIsThisWorkBookBad = Not (ThisWorkbook.CodeName = "ThisWorkbook")
  Exit Function
ErrLabel:
  ErrorIsThisWorkBookBad = True
End Function

I called this code in a Private Sub Auto_Open()

Private Sub Auto_Open()
  RenameThisWorkbookToEnglish

  If ErrorIsThisWorkBookBad Then
    Debug.Print ("Workbook Is Bad.")
  End If
End Sub

This code reports as bad. The VBE shows a ThisWorkbook module, but its name is still DieseArbeitsmappe.

Even though the Debug.Print in the Auto_Open reports bad, a later button click function that uses ThisWorkbook is good. Then saving results in multiple ThisWorkbooks (i.e. ThisWorkbook, ThisWorkbook1).

So it kind of works, but not really.

Other notes: I only have English installed on my machine. I do not have access to all of the spreadsheets, but am writing VBA that will be put in them.

Main Question: How to change the localization or ??? to make ThisWorkbook a valid variable?

Upvotes: 0

Views: 274

Answers (1)

ExcelIsFun_Sometimes
ExcelIsFun_Sometimes

Reputation: 45

Thanks to Storax, I found my error.

As noted in the comments, I was receiving a false positive from ErrorIsThisWorkBookBad on the line ThisWorkbook.CodeName = "ThisWorkbook" when ThisWorkbook.CodeName was DieseArbeitsmappe.

I rewrote ErrorIsThisWorkBookBad (below) and tested on a corrupt workbook (i.e. a workbook with ThisWorkbook and ThisWorkbook1 like this). The corrupted workbook did report an error correctly.

Problem solved.

Function ErrorIsThisWorkBookBad() As Boolean
  On Error GoTo ErrLabel

  'Dummy call just to test if ThisWorkbook creates error.
  If ThisWorkbook.Name <> "" Then
  End If

  'Made it here, there is no error and ThisWorkbook is valid.
   ErrorIsThisWorkBookBad = False

   Exit Function
ErrLabel:
  ErrorIsThisWorkBookBad = True
End Function

Upvotes: 1

Related Questions