Function for Roman Numerals

[Guest]

What function is the inverse of =ROMAN()? That is, what function returns a
numeric value given a Roman numeral as its argument?

 

[Bernie Deitrick]

No. You need to use a UDF. See the code below, from a post by Dave
Peterson (who couldn't remember the original source).

HTH,
Bernie
MS Excel MVP


Option Explicit
Function Arabic(Roman)
'Declare variables
Dim Arabicvalues() As Integer
Dim convertedvalue As Long
Dim currentchar As String * 1
Dim i As Integer
Dim message As String
Dim numchars As Integer

'Trim argument, get argument length, and redimension array
Roman = LTrim(RTrim(Roman))
numchars = Len(Roman)
If numchars = 0 Then 'if arg is null, we're outta here
Arabic = ""
Exit Function
End If

ReDim Arabicvalues(numchars)
'Convert each Roman character to its Arabic equivalent
'If any character is invalid, display message and exit
For i = 1 To numchars
currentchar = Mid(Roman, i, 1)
Select Case UCase(currentchar)
Case "M": Arabicvalues(i) = 1000
Case "D": Arabicvalues(i) = 500
Case "C": Arabicvalues(i) = 100
Case "L": Arabicvalues(i) = 50
Case "X": Arabicvalues(i) = 10
Case "V": Arabicvalues(i) = 5
Case "I": Arabicvalues(i) = 1
Case Else
Arabic = "Sorry, " & Roman & " is not a valid Roman numeral!
"
Exit Function
End Select
Next i

'If any value is less than its neighbor to the right,
'make that value negative
For i = 1 To numchars - 1
If Arabicvalues(i) < Arabicvalues(i + 1) Then
Arabicvalues(i) = Arabicvalues(i) * -1
End If
Next i
'Build Arabic total
For i = 1 To numchars
Arabic = Arabic + Arabicvalues(i)
Next i

End Function

 

[JE McGimpsey]

Searching the archives,

http://groups.google.com/advanced_group_search?as_ugroup=*excel*

Dave Peterson posted a UDF of unknown provenance:


http://groups-beta.google.com/group/microsoft.public.excel.worksheet.func
tions/msg/63126e7ebd85d9e5?hl=en

 

[Bernie Deitrick]

And just to get this into the archives, here's another UDF, apparently by
Laurent Longre.

HTH,
Bernie
MS Excel MVP

'----------------------------------------------------------------------
' Conversion d'un nombre < 4000 en chiffres romains (style "classique")
' vers un nombre en chiffres arabes
'----------------------------------------------------------------------
'Laurent Longre, mpfe

Function ROMINVERSE(Nombre As String)

Const Symb = "IVXLCDM"
Dim I As Integer, J As Integer
Dim K As Integer, L As Integer, S As Integer
Dim C As String * 1, Prec As Boolean

On Error GoTo Erreur
I = Len(Nombre)
Do
K = InStr(1, Symb, Mid$(Nombre, I, 1))
If K = 0 Or K = J Then Err.Raise xlErrValue
S = IIf(K Mod 2, 1, 5) * 10 ^ ((K - 1) \ 2)
If K < J Then
If Not Prec Then Err.Raise xlErrValue
Select Case Mid$(Nombre, I, 2)
Case Is = "ID", Is = "IM", Is = "VX", Is = "VD", _
Is = "VM", Is = "LC", Is = "DM"
Err.Raise xlErrValue
End Select
ROMINVERSE = ROMINVERSE - S
I = I - 1
Prec = False
ElseIf K Mod 2 Then
C = Mid$(Symb, K, 1)
L = 0
Do
If Mid$(Nombre, I, 1) = C Then
If L = 3 Then Err.Raise xlErrValue
ROMINVERSE = ROMINVERSE + S
I = I - 1
L = L + 1
Else
Prec = L = 1
Exit Do
End If
Loop While I
Else
ROMINVERSE = ROMINVERSE + S
I = I - 1
Prec = True
End If
J = K
Loop While I
Exit Function

Erreur:
ROMINVERSE = CVErr(Err)

End Function

 

[Harlan Grove]

Bernie Deitrick wrote...

No. You need to use a UDF. . . .

No you don't. This can be done with formulas, and not terribly complex
ones.

=SUMPRODUCT(LEN(x)-LEN(SUBSTITUTE(x,{"M";"D";"C";"L";"X";"V";"I"},"")),
{1000;500;100;50;10;5;1})
+SUMPRODUCT(LEN(C1)-LEN(SUBSTITUTE(C1,{"CM";"CD";"XC";"XL";"IX";"IV";0},"")),
{-100;-100;-10;-10;-1;-1;0})

 

[Bernie Deitrick]

Harlan,

Then I should have been clearer: "No, there is no built-in Excel function,
but you can use ....."

HTH,
Bernie
MS Excel MVP

 

[Harlan Grove]

Bernie Deitrick wrote...

Then I should have been clearer: "No, there is no built-in Excel function,
but you can use ....."

....

OK, but there's a trade-off between udfs and long formulas. Formulas
need to be really long, really complicated and process LOTS of data
before they become anywhere near as slow as udfs. And formulas using
only built-in functions don't cause problems with macro security
settings.