Converting Numbers to Says in Word of Text

Some time we need numbers says in word in a excel cell.

We can use excel function to convert it.


In that function there are several part of calculation :
1. Difinition of words to be used in arrays
2. Looping of three series of numbers.
3. Defining hundreds and teens.

Function SaysInText(Snumber As Double) As String
Dim a, b

'part 1. Definition of words to be used.
a = Array("", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")
b = Array("", "Ten ", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ", "Hundred ")
c = Array("", "Thousand ", "Million ", "Billion ")
d = Array("", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")

tempor = Snumber

'Part 2. Looping for each three series of numbers.
i = 0
While tempor > 0

'part 3. Check the teens
    If (Int(tempor / 10) Mod 10) = 1 And (tempor Mod 10) <> 0 Then
        p1 = d(tempor Mod 10)
    Else
        P2 = b(Int(tempor / 10) Mod 10)
        p1 = a(tempor Mod 10)
    End If

'Part 4. Check the hundreds
    If (Int(tempor / 100) Mod 10) = 0 Then
        p3 = ""
    Else
        p3 = a(Int(tempor / 100) Mod 10) & "Hundred "
    End If

    SaysInText = p3 & P2 & p1 & c(i) & SaysInText
 
    tempor = Int(tempor / 1000)
    i = i + 1
Wend

End Function

You can find the sample file in the right Box

Converting entered date list to Status

Usually, if we want to convert entered date in the row list to become remark of status we use "IF" formula, like example below.

But actually we can use function in VBA to convert it. Just insert a module in the VBA project and copy paste herebelow
function .

Public Function StatusRem(dated As Range)

    For i = dated.Columns.Count To 1 Step -1

        If dated.Cells(1, i).Value <> "" Then

            StatusRem = Cells(1, dated.Column + i - 1).Value

            Exit Function

        End If

    Next i

    StatusRem = ""

End Function

Now we can use that function as formula as example below :



This formula will return a text value of column header no matter how may column are in the list.

Excel as Database with Form

We can use Excel as a database, but some time we need a form for entering and updating data.
You may download the sample file i.e "FormDatabase.xls", for your reference.

in ID:

Kita bisa menggunakan Excel sebagai basis data, akan tetapi terkadang kita memerlukan sebuah form untuk pengisian dan pengubahan data. File contoh "FormDatabase.xls" dapat di-download sebagai referensi.

Controlling Execution - GoTo Statements

The most straightforward way to change the flow of a program is to use a GoTo statement. The statement simply transfers program execution to other line instruction, which must be preceded by a label (a text string followed by a colon, or a number with no colon). In the example below, the execution program firstly showing message box "Step 1" and after that jump to label Step3 and showing message box "Step 3" without executing message box "Step 2"


In ID :

Yang memiliki cara langsung untuk mengubah aliran program ialah menggunakan pernyataan GoTo. Pernyataan ini dengan simpel memindahkan aliran eksekusi program ke pernyataan pada baris yang lain, dilakukan dengan cara memberikan sebuah label. (sebuah teks string diikuti oleh tanda : atau sebuah angka tanpa tanda :).

Pada contoh di bawah, eksekusi program mula-mula menampilkan pesan "Step 1", kemudian akan melompat ke label Step3 untuk menampilkan pesan "Step 3" tanpa mengeksekusi penampilan pesan "Step 2".


Example :

Sub Coba()
     Msgbox "Step 1"
GoTo Step3

     Msgbox "Step 2"

Step3:
    Msgbox "Step 3"

End Sub

Controlling Execution of VBA on Macro Excel

Some VBA procedures star at the top and progress line by line to the bottom. Most of recorded Macros work in this fashion. Sometime it needed to control the flow of routines by skipping over some statements, executing some statements multiple times and testing conditions to determine what the routine does next.

We can use i.e.

• GoTo statements
• If-Then construcs
• Select Case constructs
• Fo-Next loops
• Do While loops
• Do Until loops

In ID :

Beberapa Prosedur VBA memulai eksekusi perintah-perintah dari atas dan berlanjut baris per baris sampai di baris terakhir. Macro yang terekam sebagian besar berlaku seperti hal tersebut. Akan tetapi suatu saat diperlukan untuk mengkontrol aliran dari rutinitas yang ada dengan melompati beberapa pernyataan, mengeksekusi pernyataan beberapa kali dan melakukan pemeriksaan kondisi untuk menentukan rutinitas apa yang akan dilakukan kemudian.

Untuk melakukan hal tersebut dapat digunakan pernyataan-pernyataan tesebut di atas.