Below you will find a ready-made user-defined function in VBA that translates any number from 0 to 9 into its textual representation, i.e. in the amount in words. Before use, this function must be added to your book. For this:
- press the keyboard shortcut ALT + F11to open the Visual Basic Editor
- add new empty module via menu Insert – Module
- copy and paste the text of this function there:
Function SUM(n As Double) As String Dim Nums1, Nums2, Nums3, Nums4 As Variant Nums1 = Array("", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine") Nums2 = Array("", "ten", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", _ "eighty ", "ninety") Nums3 = Array("", "one hundred", "two hundred", "three hundred", "four hundred", "five hundred", "six hundred", "seven hundred", _ "eight hundred", "nine hundred") Nums4 = Array("", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine") Nums5 = Array("ten " , "eleven", "twelve", "thirteen", "fourteen", _ "fifteen", "sixteen", "seventeen", "eighteen", "nineteen") If n <= 0 Then SUMWRITE = "zero" Exit Function End If 'divide the number into digits using the helper function Class ed = Class(n, 1) dec = Class(n, 2) sot = Class(n, 3) tys = Class(n, 4) dectys = Class(n , 5) sottys = Class(n, 6) mil = Class(n, 7) decmil= Class(n, 8) 'check millions Select Case decmil Case 1 mil_txt = Nums5(mil) & "millions " GoTo www Case 2 To 9 decmil_txt = Nums2(decmil) End Select Select Case mil Case 1 mil_txt = Nums1(mil) & "million " Case 2, 3, 4 mil_txt = Nums1(mil) & "million " Case 5 To 20 mil_txt = Nums1(mil) & "millions " End Select www: sottys_txt = Nums3(sottys) ' check thousands Select Case dectys Case 1 tys_txt = Nums5(tys) & "thousands " GoTo eee Case 2 To 9 dectys_txt = Nums2(dectys) End Select Select Case tys Case 0 If dectys > 0 Then tys_txt = Nums4(tys) & "thousands " Case 1 tys_txt = Nums4(tys) & "thousand " Case 2, 3, 4 tys_txt = Nums4(tys) & "thousands " Case 5 To 9 tys_txt = Nums4(tys) & "thousands " End Select If dectys = 0 And tys = 0 And sottys <> 0 Then sottys_txt = sottys_txt & "thousands" eee: sot_txt = Nums3(sot) 'check tens Select Case dec Case 1 ed_txt = Nums5(ed) GoTo rrr Case 2 To 9 dec_txt = Nums2(dec) End Select ed_txt = Nums1 (ed) rrr: 'form the final row with SUM IN SPEECH = decmil_ txt & mil_txt & sottys_txt & dectys_txt & tys_txt & sot_txt & dec_txt & ed_txt End Function ' helper function to extract from the number of digits Private Function Class(M, I) Class = Int(Int(M - (10 ^ I) * Int(M / (10 ^ I))) / 10 ^ (I - 1)) End Function Save the file (if you have Excel 2007 or 2010, then the file type must be macro-enabled, i.e. xlsm format!) and return to Excel. Now you can insert the created function into any cell of the worksheet in the usual way – through the function wizard (button fx in formula bar, category User Defined) or simply by typing it in the cell manually and specifying the cell with the amount as an argument:
If you need to add pennies to the received text, then you can use a slightly more complex construction:
u3d SUM IN WRITE (A3) & ” rub. “&TEXT((A3-INTEGER(A100))*00;”XNUMX″)&”cop.”
u3d SUM IN WRITE (A3) & ” rub. “&TEXT((A3-INT(A100))*00;”XNUMX″)&”cop.”
Then, for example, for the number 35,15, the result of the function will look like “thirty-five rubles. 15 kop.”
- A more powerful version of the function with rubles and kopecks in / English from the PLEX add-on
- What are macros, where to insert macro code, how to use them