how to merge two VBA codes in one?

[yazidioubi]

I have these two VBA codes, to get the result, I have to put the following excel formula to do the trick : =CCPA(A1)&(MAJUSCULE(A1&MAJUSCULE(RIP(A1)))).
I want to merge the two together in a function named for example : Account, so by just typing =Account(A1), it will give me the result obtained from the formula : =CCPA(A1)&(MAJUSCULE(A1&MAJUSCULE(RIP(A1))))

The codes are :

Function CCPA(X)
    Select Case Len(X)
        Case 0
            c1 = "00799999000000000"
        Case 1
            c1 = "00799999000000000"
        Case 2
            c1 = "0079999900000000"
        Case 3
            c1 = "007999990000000"
        Case 4
            c1 = "00799999000000"
        Case 5
            c1 = "0079999900000"
        Case 6
            c1 = "007999990000"
        Case 7
            c1 = "00799999000"
        Case 8
            c1 = "0079999900"
        Case 9
            c1 = "007999990"
        Case 10
            c1 = "00799999"
End Select
    CCPA = c1
End Function
    
Public Function RIP(Cle_RIP As String) As String
Cle_RIP = Right(Cle_RIP, 10)
If Cle_RIP = "" Then
    Cle_RIP = 0
End If
RIP = Cle_RIP * 100
RIP = RIP - 97 * Int(RIP / 97)
RIP = RIP + 85
If RIP < 97 Then
    RIP = RIP + 97
Else
    RIP = RIP
End If
RIP = RIP - 97
RIP = 97 - RIP
If RIP < 10 Then
    RIP = "0" & RIP
Else
    RIP = RIP
End If
End Function

[June7]

Simplest is to just call both functions:

Public Function Account(x)
Account = CCPA(x) & (MAJUSCULE(x & MAJUSCULE(RIP(x))))
End Function

[Aussiebear]

Welcome to VBAX yazidioubi. In this forum we wrap our code with code tags, please refer to the first line in my signature to see this. Now getting into your formula....
Can you tell us what majuscule means in your language because to us majuscule means Large letter or Uppercase. In fact it would be significantly more effective if you were to post a workbook with an example of the data you are using.

In relation to your vba codes. In the first function both Case 0 and Case 1 have the same values, is this correct or a typo?
In the second Function, I am struggling to understand the logic of your arguments here,

Cle_RIP = Right(Cle_RIP,10)

appears to be circular reference to me. Logically maybe if you had written

Cle_RIP1 = Right(Cle-RIP,10)

.

Anyway the next bit, not withstanding my point from above

Cle_RIP= Right(Cle_RIP,10)
If Cle-RIP = " " then
Cle-RIP = 0

could be better constructed as

If Cle_RIP <> "" Then
    Cle_RIP= Right(Cle-RIP,10)
Else
    Cle-RIP = 0
End If

Logically though, both of these will fail as the program will not know which Cle_RIP to use as the value. Hence the importance of to name values correctly.

Similarly the following should also fail

RIP = Cle_RIP * 100
RIP = RIP - 97 * INT(RIP/97)
RIP = RIP + 85

How does the program understand which RIP value to use and when?

Then you have added further confusion with the following

....
End If
RIP = RIP - 97
RIP = 97 + RIP
If RIP <10 then
    RIP = 0 + RIP
Else
RIP = RIP
End If

How does the program know when and how to use the value RIP?

Secondly the next few lines could be simply written as

If RIP <10 Then
    RIP = RIP
End If

Please attach a workbook with sample data to assist clearing up the confusion.

[yazidioubi]

Welcome to VBAX yazidioubi. In this forum we wrap our code with code tags, please refer to the first line in my signature to see this. Now getting into your formula....
Can you tell us what majuscule means in your language because to us majuscule means Large letter or Uppercase. In fact it would be significantly more effective if you were to post a workbook with an example of the data you are using.

In relation to your vba codes. In the first function both Case 0 and Case 1 have the same values, is this correct or a typo?
In the second Function, I am struggling to understand the logic of your arguments here,

Cle_RIP = Right(Cle_RIP,10)

appears to be circular reference to me. Logically maybe if you had written

Cle_RIP1 = Right(Cle-RIP,10)

.

Anyway the next bit, not withstanding my point from above

Cle_RIP= Right(Cle_RIP,10)
If Cle-RIP = " " then
Cle-RIP = 0

could be better constructed as

If Cle_RIP <> "" Then
    Cle_RIP= Right(Cle-RIP,10)
Else
    Cle-RIP = 0
End If

Logically though, both of these will fail as the program will not know which Cle_RIP to use as the value. Hence the importance of to name values correctly.

Similarly the following should also fail

RIP = Cle_RIP * 100
RIP = RIP - 97 * INT(RIP/97)
RIP = RIP + 85

How does the program understand which RIP value to use and when?

Then you have added further confusion with the following

....
End If
RIP = RIP - 97
RIP = 97 + RIP
If RIP <10 then
    RIP = 0 + RIP
Else
RIP = RIP
End If

How does the program know when and how to use the value RIP?

Secondly the next few lines could be simply written as

If RIP <10 Then
    RIP = RIP
End If

Please attach a workbook with sample data to assist clearing up the confusion.

[yazidioubi]

forget about 'majuscule' it's not needed, I can do without it.
so the formula will be:
=CCPA(A1)&(A1&(RIP(A1)))

[p45cal]

Also to be found cross posted, without links, here:
https://www.excelforum.com/excel-pro...es-in-one.html
https://stackoverflow.com/questions/...a-codes-in-one

[Aussiebear]

Hmmmm... yet another who doesn't care about anyone other than themselves.