Well today I found very useful for macro code in Excel, which I think can be useful to anyone who needs to create an Excel formula such as printing KP, invoices or any other documentation, using the amount written in words. Excel does not contains such a function ready (I think it's a big sneezes.) It was therefore necessary to create such a program, which will be able to retrieve data in the form of numbers / currency and issue of new cells in the form of verbal feedback. As you can see the macro works on numbers from -99 to 99 999.99 999.99 (no but if someone has a turnover bigger, rotates more cash, I would probably get it ordered from a professional program of trained computer science, my gray beetles we must deal differently).
text macro is as follows:
verbally Function (x As Variant) As String 'for the numbers from -99 to 99 999.99 999.99
If x < 0 Then w = w & "minus "
x = Format (Abs (x), 000 000 000.00): m = Left (x, 3) t = Mid (x, 5, 3) j = Mid (x, 9, 3): g = "0" & \u200b\u200bRight (x, 2) Select Case m
Case 0 Case 1
a = "one million" Case Else
w = w & three (m)
If Mid (m, 2, 1 ) <> 1 And (Right (m, 1) = 2 Or Right (m, 1) = 3 Or Right (m, 1) = 4) Then a = a & "million" Else a = a & "million"
End
Select Select Case t Case
0
Case 1 w = w & "one thousand" Case Else
in three = in & (t)
If Mid (t, 2, 1) <> 1 And (Right (t, 1) = 2 Or Right (t, 1) = 3 Or Right (t, 1) = 4) Then a = a & "thousands" Else a = a & "thousands"
End Select Select Case j Case 0
If m = 0 and T = 0 Then w = w & "zero-gold 'Else w = w &' golden '
Case 1 If m = 0 and T = 0 Then a = a &" a gold' Else w = w & " a gold "Case Else
w = w & three (j)
If Mid (j, 2, 1) <> 1 And (Right (j, 1) = 2 Or Right (j, 1) = 3 Or Right (j , 1) = 4) Then a = a & "gold" Else a = a & "gold"
End Select Select Case Case 0 g
w = w & "zero cents"
Case 1 w = w & "one penny" Case Else
w = w & three (g)
If Mid (g, 2, 1) <> 1 And (Right (g, 1) = 2 Or Right (g, 1) = 3 Or Right (g, 1) = 4) Then a = a & "cents" Else a = a & "cents" End Select
verbally = w End Function
Function three (x As Variant) As String
x3 = Val (Left (x, 1)) x2 = Val (Mid (x, 2, 1)): x1 = Val (Right (x, 1)) x3 If
= 9 Then a = a & "nine hundred"
If x3 = 8 Then a = a & "eight hundred"
If x3 = 7 Then a = a & "seven hundred"
If x3 = 6 Then w = w & "six hundred"
If x3 = 5 Then w = w & "five hundred"
If x3 = 4 Then w = w & "four"
If x3 = 3 Then w = w & "three hundred" If
x3 = 2 Then w = w & "two hundred"
If x3 = 1 Then a = a & "hundred"
If x2 = 9 Then a = a & "ninety"
If x2 = 8 Then a = a & "eighty"
If x2 = 7 Then a = a & "seventy"
If x2 = 6 Then w = w & "sixty"
If x2 = 5 Then w = w & "fifty"
If x2 = 4 Then w = w & "forty "
If x2 = 3 Then w = w & "thirty"
If x2 = 2 Then w = w & "twenty"
If x2 = 1 Then
If x1 = 9 Then a = a & "nineteen"
If x1 = 8 Then w = w & "eighteen"
If x1 = 7 Then a = a & "seventeen"
If x1 = 6 Then w = w & "sixteen"
If x1 = 5 Then a = a & "fifteen"
If x1 = 4 Then w = w & "fourteen"
If x1 = 3 Then w = w & "thirteen"
If x1 = 2 Then w = w & "twelve"
If x1 = 1 Then a = a & "eleven" If
x1 = 0 Then w = w & "Ten"
End If If 2x <>
1 Then If x1 = 9 Then a = a & "nine"
If x1 = 8 Then a = a & "eight"
If x1 = 7 Then a = a & "seven "
If x1 = 6 Then w = w &" six "
If x1 = 5 Then w = w &" five "
If x1 = 4 Then w = w &" four "
If x1 = 3 Then w = w & "three"
If x1 = 2 Then w = w & "two"
If x1 = 1 Then a = a & "a" End If
three = a End Function
And now how it works. We enter into the Excel. Press Alt + F11. Appears we have a window of Visual Basic. From the top menu, select Insert -> Module and paste our code. Close the Visual Basic and now we can replace the values \u200b\u200bobtained from the selected cell by typing = new words (eg, A5).
This macro should be assigned to a digital security certificate, because as each macro can be stopped by Excel after turning off and turning on the program. But the thing useful. I myself hold the text of this security code in the file. Txt
not remember his name the author of this code, but in my opinion, has created something very useful, what exactly should be in a standard Excel.
