Compile Error: compile error constant expression required.

[countryfan_nt]

Hello friends, Hope all is well!

Please help me with the bug below. the code converts Arabic numbers to text.

When I apply the code I get: Compile Error: compile error constant expression required.

The highlight is on: .Value
of the line: Private Const MyBegTx As String = Sheets("ARDB").Range("A1").Value

Please help me make the code work.

Thank you so much in advance!

Option Explicit

Private Const MyBegTx As String = Sheets("ARDB").Range("A1").Value

Private Const MyTNum As String = Sheets("ARDB").Range("A2").Value
'==========================================="
'x xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
'==========================================="

Function kh_TextNum(Num As String, Optional sex As Boolean = False, Optional sNameCurr As String = "", Optional pNameCurr As String = "", Optional NameCurrDec As String = "", Optional Decimal_Count As Byte = 2) As String
Dim Spp, zt
Dim i%, ii%, pr%
Dim MyMid$, nCurr$, Txt$, Txt1$, Txt2$
'======================================
If Not IsNumeric(Num) Then GoTo kh_Exit
Spp = Split("/" & MyTNum, "/")
ii = UBound(Spp)
If Num < 0 Then Num = Abs(Num)
'======================================
If Val(Num) > Val(String((ii + 1) * 3, "9") & ".999") Then GoTo kh_Exit
'======================================
nCurr = sNameCurr & "-" & IIf(pNameCurr = "", sNameCurr, IIf(sNameCurr = "", "", pNameCurr))
'======================================
Txt1 = Format(Num, String((ii + 1) * 3, "0") & ".000")
For i = 0 To ii
            MyMid = Mid(Txt1, (i * 3) + 1, 3)
            If MyMid Then
                        zt = Mid(Txt1, (i * 3) + 4, Len(Txt1))
                        zt = IIf(ii - i, Int(zt), zt)
                        Txt2 = IIf(ii - i, Trim(Spp(ii - i)), nCurr)
                        pr = 1 + IIf(ii - i, 1, CInt(sex))
                        Txt = Txt & IIf(Len(Txt), Sheets("ARDB").Range("A21").Value, "") & kh_nText(MyMid, Txt2, pr, zt, CBool(sNameCurr <> ""))
            End If
            If i = ii Then If MyMid = 0 Then Txt = Txt & IIf(Len(Txt), " ", Sheets("ARDB").Range("A3").Value) & sNameCurr
Next
'======================================
Txt = MyBegTx & Txt & kh_dText(Num, sNameCurr, NameCurrDec, Decimal_Count)
'======================================
kh_Exit:
kh_TextNum = Trim(Txt)
End Function

Private Function kh_nText(ByVal iNum As String, ByVal oMm As String, ByVal ibs As Integer, ByVal z As Boolean, ByVal tCu As Boolean) As String
Dim Sp
Dim Num1%, Num2%, Num3%
Dim oM$, S$, S1$, nT$, nT0$, nT1$, nT2$
'======================================
Sp = Split(Sheets("ARDB").Range("A4").Value, ",")
'======================================
If ibs Then S = Sheets("ARDB").Range("A5").Value: Sp(1) = Sp(0): Sp(2) = Sheets("ARDB").Range("A8").Value: Sp(11) = Sheets("ARDB").Range("A7").Value: Sp(12) = Sheets("ARDB").Range("A6").Value Else S1 = Sheets("ARDB").Range("A5").Value
oM = Trim(Split(oMm, "-")(0))
'======================================
Num1 = Left(iNum, 1)
Num2 = Right(iNum, 2)
Select Case Num1
            Case 1:   nT0 = Sheets("ARDB").Range("A10").Value
            Case 2:   nT0 = Sheets("ARDB").Range("A12").Value & IIf(ibs = 2, IIf(Num2 < 3, "", Sheets("ARDB").Range("A11").Value), IIf(Num2 = 0 And oM <> "", "", Sheets("ARDB").Range("A11").Value))
            Case 3 To 9: nT0 = Sp(Num1) & Sheets("ARDB").Range("A10").Value
End Select
'=========================================
Num1 = Right(iNum, 2)
Select Case Num1
            Case 1, 2:        If nT0 <> "" Then If ibs = 2 Then nT0 = nT0 & " " & oM
            Case 11 To 99: If oM <> "" Then If ibs Then If z Then oM = oM & Sheets("ARDB").Range("A13").Value
End Select
'-----------------------------------------
Select Case Num1
            Case 1
                        nT = IIf(oM = "", Sp(0) & S1, oM)
                        oM = IIf(ibs <> 2 And oM <> "", Sp(0) & S1, "")
            Case 2
                        nT = IIf(oM = "", Sp(Num1), Replace(oM, Sheets("ARDB").Range("A16").Value, Sheets("ARDB").Range("A15").Value) & IIf(z = 0 And ibs = 2 And tCu, Sheets("ARDB").Range("A22").Value, Sheets("ARDB").Range("A14").Value))
                        oM = IIf(ibs <> 2 And oM <> "", Sp(Num1), "")
            Case 3 To 10
                        oM = Trim(Split(oMm, "-")(1))
                        nT = Sp(Num1) & S
            Case 11, 12
                        nT = Sp(Num1) & Sp(10) & S1
            Case 13 To 19
                        nT = Sp(Num1 - 10) & S & " " & Sp(10) & S1
            Case 20 To 99
                        Num2 = Right(Num1, 1)
                        Num3 = Left(Num1, 1)
                        If Num3 = 2 Then nT1 = Sheets("ARDB").Range("A18").Value Else nT1 = Sp(Num3) & Sheets("ARDB").Range("A17").Value
                        nT2 = Sp(Num2) & IIf(Num2 > 2, S, "") & Sheets("ARDB").Range("A19").Value & nT1
                        If Num2 = 0 Then nT2 = nT1
                        nT = nT2
End Select
'======================================
S = IIf(nT = "" Or iNum < 100, "", Sheets("ARDB").Range("A21").Value)
nT = Replace(nT, Sp(8) & Sheets("ARDB").Range("A9").Value, Sp(8) & Sheets("ARDB").Range("A20").Value)
kh_nText = Trim(nT0 & S & nT & " " & oM)
'======================================
End Function


Private Function kh_dText(ByVal dNum As String, ByVal NCur As String, ByVal Ndec As String, ByVal co As Byte) As String
Dim Td$, Td1$
On Error GoTo 1
If NCur = "" Then Ndec = ""
Td = Format(Round(CCur(dNum - Int(dNum)), co), "0." & String(co, "0"))
If Td = 0 Or Td = 1 Then Td1 = "": GoTo 1
If Len(Ndec) Then Ndec = " " & Ndec: Td1 = Td * CVar("1" & String(co, "0")) Else Ndec = " " & NCur: Td1 = Td
Td1 = "  " & Sheets("ARDB").Range("A21").Value & Chr(40) & Td1 & Chr(41) & Ndec
1: kh_dText = Td1
End Function

 

[steve the fish]

You cant do that with a constant. Try like this:

Dim MyBegTx As String
MyBegTx = Sheets("ARDB").Range("A1").Value

 

[countryfan_nt]

thank you, OK I did the below
but got a new bug: Compile error: "Invalid outside procedure". and A1 is highlighted of the line "MyBegTx".

Option Explicit

Dim MyBegTx As String
Dim MyTNum As String

MyBegTx = Sheets("ARDB").Range("A1").Value
MyTNum = Sheets("ARDB").Range("A2").Value

 

[steve the fish]

You need to put that within the function.

 

[countryfan_nt]

So sorry to bother again, I made some adjustments to the code and I got: compile error constant expression required.

.Value was highlighted; it is for the line: Optional NCurr_Si As String = Sheets("ARDB").Range("A4").Value, _


what changes do you suggest I should do? Thanks a lot, really.

Option Explicit

Function CurrText(Num As String, _
Optional gen As Boolean = True, _
Optional NCurr_Si As String = Sheets("ARDB").Range("A4").Value, _
Optional NCurr_Pl As String = Sheets("ARDB").Range("A5").Value, _
Optional dGEN As Boolean = True, _
Optional NCurrDec_Si As String = Sheets("ARDB").Range("A6").Value, _
Optional NCurrDec_Pl As String = Sheets("ARDB").Range("A7").Value, _
Optional Decimal_Count As Byte = 2) _
As String

'======================================
Dim Spp, zt
Dim i%, ii%, pr%
Dim MyMid$, nCurr$, Txt$, Txt1$, Txt2$


Private Const MyBegTx As String = ""
Private Const MyEndTx As String = Sheets("ARDB").Range("A1").Value

Private Const MyTNum As String = Sheets("ARDB").Range("A2").Value
'==============================================================================================================================================="
Private Const wow As String * 2 = Sheets("ARDB").Range("A30").Value

'======================================
If Not IsNumeric(Num) Then GoTo kh_Exit
If Num = 0 Then Txt = MyBegTx & Sheets("ARDB").Range("A8").Value & NCurr_Si: GoTo kh_Exit
'======================================
Spp = Split("/" & MyTNum, "/")
ii = UBound(Spp)
If Num < 0 Then Num = Abs(Num)
'======================================
If Val(Num) > Val(String((ii + 1) * 3, "9") & ".999") Then GoTo kh_Exit
'======================================
nCurr = NCurr_Si & "-" & IIf(NCurr_Pl = "", NCurr_Si, IIf(NCurr_Si = "", "", NCurr_Pl))
'======================================
Txt1 = Format(Num, String((ii + 1) * 3, "0") & ".000")
For i = 0 To ii
    MyMid = Mid(Txt1, (i * 3) + 1, 3)
    If MyMid Then
        zt = Mid(Txt1, (i * 3) + 4, Len(Txt1))
        zt = IIf(ii - i, Int(zt), 1)
        Txt2 = IIf(ii - i, Trim(Spp(ii - i)), nCurr)
        pr = 1 + IIf(ii - i, 1, CInt(gen))
        Txt = Txt & IIf(Len(Txt), wow, "") & kh_nText(MyMid, Txt2, pr, zt, CBool(NCurr_Si <> ""))
    End If
    If i = ii Then If MyMid = 0 Then Txt = Txt & IIf(Len(Txt), " " & NCurr_Si, IIf(Decimal_Count = 0, Sheets("ARDB").Range("A9").Value, ""))
Next
'======================================
Txt = MyBegTx & Txt & kh_dText(Num, NCurr_Si, Trim(NCurrDec_Si), Decimal_Count, Trim(NCurrDec_Pl), dGEN) & MyEndTx
'======================================
kh_Exit:
CurrText = Trim(Txt)
End Function

 

[Fluff]

You cannot pass a variable value like that, it needs to be a fixed value.
One option would be to make it a required argument rather than an optional one.

Also these lines will fail

Private Const MyBegTx As String = ""
Private Const MyEndTx As String = Sheets("ARDB").Range("A1").Value

Private Const MyTNum As String = Sheets("ARDB").Range("A2").Value
'==============================================================================================================================================="
Private Const wow As String * 2 = Sheets("ARDB").Range("A30").Value

because you cannot use the word "Private" inside a sub or function like that. They will also fail, because you cannot pass a variable value to a Constant.

 

[countryfan_nt]

Thank you very much.
Can you please help me edit the code so that it does work.
It will help me learn what went wrong. Much appreicated

 

[Fluff]

Fraid not, I have absolutely no idea what the code is doing, what it's designed to do, what you want it to do, or how you intend to use it.

 

[countryfan_nt]

The code is supposed to convert English Numbers, to Arabic text.

 

[Fluff]

I'm afraid that doesn't help me. Reverse engineering code can be very difficult (IMO) and there are so many arguments & variables I have no idea what the code is doing, so cannot help any further.