How to get Arabic Month Names using Excel VBA

Question

In Excel VBA, I'm trying to put the "Arabic Name of a Month" in a Variable, and i found a way. But my way requires a buffer cell as to place values in, change cell format, retrieve the text value of the cell, and then put that value in a variable.

Here is my VBA code:

    Sub GetArabicName()
         Sheets("Sheet1").Cells(1, 1).Value = date() 
         Sheets("Sheet1").Cells(1, 1).NumberFormat = "[$-10A0000]mmmm;@" 
         ArabicMonth = Sheets("Sheet1").Cells(1, 1).Text
         MsgBox ArabicMonth & " The Arabic Name of the Month"
    End Sub

Is there a simpler way to do this using VBA and without using the buffer cell? Also, how can i make the MsgBox display the Arabic value not "?????"

Thank you in advance.

Answer 1

I cannot remove the need for the helper cell, but this gets a message-type Box to display text:

Public Declare Function MessageBoxU Lib "user32" Alias "MessageBoxW" _
                            (ByVal hwnd As Long, _
                             ByVal lpText As Long, _
                             ByVal lpCaption As Long, _
                             ByVal wType As Long) As Long

Sub GetArabicName()
    Dim ArabicMonth As String
    With Sheets("Sheet1").Cells(1, 1)
         .Value = Date
         .NumberFormat = "[$-10A0000]mmmm;@"
         .Font.Name = "Arial Unicode MS"
         ArabicMonth = .Text
    End With
    MessageBoxU 0, StrPtr(ArabicMonth), StrPtr("MsgBox Substitute"), 0
    MsgBox ArabicMonth & " The Arabic Name of the Month"
End Sub

Adapted from:

Renaud Bompuis

EDIT#1:

Based on the excellent suggestion from Axel Richter, this removes the need for the helper cell:

Sub GetArabicNames_II()
    Dim ArabicMonth As String
    ArabicMonth = Application.WorksheetFunction.Text(Date, "[$-10A0000]mmmm;@")
    MessageBoxU 0, StrPtr(ArabicMonth), StrPtr("MsgBox Substitute"), 0
End Sub