none
Macro string to cell value using utf RRS feed

  • Question

  • I had a problem displaying Arabic (non-English) strings in the Macro Editor. So I changed the Tools>Options>Editor formats font and set it to Courier(Arabic) instead of the default Courier(Western).

    Now when I type Arabic strings in the macro editor they appear fine. However when I set a cell value to this string, weird characters appear in the sheet. It used to be the other way around.

    I learnt that Excel and Macro editor both use Unicode, however the IDE does not. So I tried to use the function:

    s=strconv(s, vbfromunicode) to convert s (my Arabic string) to the code page used by the IDE so that it will appear fine on the sheet but I still got weird characters (different though than the previous ones).

    Not sure what to do? Any help?

    Wednesday, November 18, 2015 8:37 AM

Answers

  • Well, after spending 5 hours I was able to solve my problem. You need 3 steps:

    1] control panel, region settings, Administrative tab, Language for non-Unicode programs, set it to Arabic.
    This takes care of the fact that Excel IDE does not use Unicode.

    2] In the Macro editor change the font to say Courier(Arabic) or whatever(Arabic)

    3] In your macro, assign your Arabic string literal to a variable declared as a sting. Do not use undeclared variable. Set the cell value to this string variable.

    Voila and everything is fine going back and forth between the Macro Editor and the Excel sheet.

    Of course the above applies to any non-English language. Hope somebody finds this helpful and saves hours of frustration....
    • Proposed as answer by David_JunFeng Sunday, November 22, 2015 2:14 PM
    • Marked as answer by Sueyllam Sunday, November 22, 2015 3:18 PM
    Saturday, November 21, 2015 9:49 AM

All replies

  • Hmm, that means the Input language of your OS is not Arabic?

    This suggestion did not work for you?
    https://social.msdn.microsoft.com/Forums/office/en-US/a7a021ab-d810-4494-9e9b-ccea7bfcb9cc/arabic-text-problem-in-vba-editor

    If so, I'm interested to help you. But for that I need an Excel file with a sample:

    - Write an Arabic sentence into cell A1.
    - Create a simple macro like this one, but use the same sentence from A1:

    Sub Test()
      Range("A2") = "This is my first test to write an Arabic word into a cell."
    End Sub

    - Run the macro. (So I can see what happens on your PC)
    - Save the file.
    - Upload it on an online file hoster like www.dropbox.com and post the download link here.

    Run also the macro below and post the output in the immediate window here.

    Andreas.

    Sub DetectVBAEnvironment()
      'http://msdn.microsoft.com/en-us/library/gg264614.aspx
      Dim S  As String
      With Application.LanguageSettings
        S = "Version " & Application.Version & " Build " & Application.Build & vbCrLf
        S = S & _
          "Install " & .LanguageID(msoLanguageIDInstall) & vbCrLf & _
          "Interface " & .LanguageID(msoLanguageIDUI) & vbCrLf & _
          "Help " & .LanguageID(msoLanguageIDHelp) & vbCrLf
        #If VBA7 Then
          S = S & "VBA 7"
        #ElseIf VBA6 Then
          S = S & "VBA 6"
        #ElseIf VBA5 Then
          S = S & "VBA 5"
        #ElseIf Mac Then
          S = S & "VBA Mac"
        #Else
          S = S & "VBA unknown"
        #End If
        S = S & vbCrLf
        #If Win64 Then
          S = S & "Win 64"
        #ElseIf Win32 Then
          S = S & "Win 32"
        #ElseIf Win16 Then
          S = S & "Win 16"
        #Else
          S = S & "OS unknown"
        #End If
        Debug.Print S
        'MsgBox S
      End With
    End Sub

    Saturday, November 21, 2015 8:34 AM
  • Well, after spending 5 hours I was able to solve my problem. You need 3 steps:

    1] control panel, region settings, Administrative tab, Language for non-Unicode programs, set it to Arabic.
    This takes care of the fact that Excel IDE does not use Unicode.

    2] In the Macro editor change the font to say Courier(Arabic) or whatever(Arabic)

    3] In your macro, assign your Arabic string literal to a variable declared as a sting. Do not use undeclared variable. Set the cell value to this string variable.

    Voila and everything is fine going back and forth between the Macro Editor and the Excel sheet.

    Of course the above applies to any non-English language. Hope somebody finds this helpful and saves hours of frustration....
    • Proposed as answer by David_JunFeng Sunday, November 22, 2015 2:14 PM
    • Marked as answer by Sueyllam Sunday, November 22, 2015 3:18 PM
    Saturday, November 21, 2015 9:49 AM