Helmut and I are still conversing back in the older thread; but, just in
case you still can't see the responses there, here is part of my latest
posting back there which contains a correction to my code (in response to a
comment by Helmut)...
Good point about the decimal point, but easily resolved using
Format$(0,"."), which will return the localized decimal point character. I
like the idea of using Format instead of CStr, but I changed the format
pattern slightly so that when the EncodeCosts function is used as a UDF
against empty cells, nothing will be displayed instead of 0.00 (which is
what your format pattern would display). I left the format pattern returning
0.00 for a price of zero, although I guess one wouldn't normally expect that
price in a cell; however, putting 0 after the second semi-colon in my format
pattern would force the return value of 0 instead of 0.00 if that turned out
to be the desired result for zero dollars. As for allowing the OP to change
the character from a decimal point to an asterisk (or any other text string,
whether one or more character in length), I added a new last statement to my
function... currently it is commented out (which means the decimal point is
retained), however "uncommenting" it and using whatever text you want in the
Replace function call's last argument (currently set up as your favored
asterisk symbol) will make the output use that text in place of the decimal
point instead.
Function EncodeCosts(Costs As Currency) As String
Dim X As Long, DecimalPoint As String
DecimalPoint = Format$(0, ".")
EncodeCosts = Format(Costs, "0.00;;0;")
For X = 1 To Len(EncodeCosts)
If Mid(EncodeCosts, X, 1) <> DecimalPoint Then Mid(EncodeCosts, _
X, 1) = Mid("ZOTRFVXSEN", Mid(EncodeCosts, X, 1) + 1, 1)
Next
'EncodeCosts = Replace(EncodeCosts, DecimalPoint, "*")
End Function
Just in case the OP turns out to want to adopt my suggestion of using no
separating symbol (knowing that the last two characters represents the
number of pennies), here is my modified code which should work for the
international community...
Function EncodeCosts(Costs As String) As String
Dim X As Long
EncodeCosts = Format(Costs, "0.00;;0;")
For X = 1 To Len(EncodeCosts)
Mid(EncodeCosts, X) = Mid("ZOTRFVXSEN", Mid(EncodeCosts, X, 1) + 1, 1)
Next
End Function
Just as a point of information, I have never had to deal with international
issues in my programming career, hence my stumbling around on the decimal
point matter.
--
Rick (MVP - Excel)
"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>> (I've previously asked this question but didnt get any replies.)
>
> ???? Not only did you get answers to your previous posting of this
> question, but you answered some of the responses you got there!
>
> Back in your original thread, Helmut Meukel suggested converting your
> codes this way...
>
> 0 = Z(ero)
> 1 = O(ne)
> 2 = T(wo)
> 3 = (th)R(ee)
> 4 = F(our)
> 5 = (fi)V(e)
> 6 = (si)X
> 7 = S(even)
> 8 = E(ight)
> 9 = N(ine)
>
> and he also suggested leaving the decimal point in place. He posted code
> that did that. I followed up on that by suggesting the decimal point be
> omitted to make it even harder to decipher a it as a price code. I then
> posted these shorter code routines to do either of these suggestions....
>
> ' Leave the decimal point in the code
> Function EncodeCosts(Costs As Currency) As String
> Dim X As Long
> EncodeCosts = CStr(Costs)
> For X = 1 To Len(EncodeCosts)
> If Mid(EncodeCosts, X, 1) <> "." Then Mid(EncodeCosts, X, 1) = _
> Mid("ZOTRFVXSEN", Mid(EncodeCosts, X, 1) + 1, 1)
> Next
> End Function
>
> ' Omit the decimal point from the code
> Function EncodeCosts(Costs As String) As String
> Dim X As Long
> EncodeCosts = Replace(CStr(Costs), ".", "")
> For X = 1 To Len(EncodeCosts)
> Mid(EncodeCosts, X) = Mid("ZOTRFVXSEN", Mid(EncodeCosts, X, 1) + 1, 1)
> Next
> End Function
>
> --
> Rick (MVP - Excel)
Similar Threads
