Dealing with ASCII Characters

Normally ASCII character is used when some string we want to use but prohibited in VBA such as " , ) and etc.

Eg. for below command.
If sPin(1) = "key=" & Chr$(34) & "25" & Chr$(34) Then

ASCII Codes 0 - 127

0 &127;  32 [space] 64 @   96 ` 
1 &127;    33 !    65 A    97 a 
2 &127;    34 "    66 B    98 b 
3 &127;    35 #    67 C    99 c 
4 &127;    36 $    68 D    100 d 
5 &127;    37 %    69 E    101 e 
6 &127;    38 &    70 F    102 f 
7 &127;    39 '    71 G    103 g 
8 * *      40 (    72 H    104 h 
9 * *      41 )    73 I    105 i 
10 * *     42 *    74 J    106 j 
11 &127;   43 +    75 K    107 k 
12 &127;   44 ,    76 L    108 l 
13 * *     45 -    77 M    109 m 
14 &127;   46 .    78 N    110 n 
15 &127;   47 /    79 O    111 o 
16 &127;   48 0    80 P    112 p 
17 &127;   49 1    81 Q    113 q 
18 &127;   50 2    82 R    114 r 
19 &127;   51 3    83 S    115 s 
20 &127;   52 4    84 T    116 t 
21 &127;   53 5    85 U    117 u 
22 &127;   54 6    86 V    118 v 
23 &127;   55 7    87 W    119 w 
24 &127;   56 8    88 X    120 x 
25 &127;   57 9    89 Y    121 y 
26 &127;   58 :    90 Z    122 z 
27 &127;   59 ;    91 [    123 { 
28 &127;   60 <    92 \    124 | 
29 &127;   61 =    93 ]    125 } 
30 &127;   62 >    94 ^    126 ~ 
    31 &127;   63 ?    95 _    127 &127; 



&127; These characters aren't supported by Microsoft Windows.

* * Values 8, 9, 10, and 13 convert to backspace, tab, linefeed, and carriage return characters, respectively. They have no graphical representation but, depending on the application, can affect the visual display of text.

Workbooks Handling

Create new Workbooks form VBA

Set newBook = Workbooks.Add

    With newBook

        .Title = "List of members"

        .Subject = "New member"

        .SaveAs filename:="Association.xls"

    End With

Add a new Workbooks
Dim wb As Workbook

Set wb = Application.Workbooks.Add

Save a new Workbooks as ".xls"
wb.SaveAs "NewWorkbook"

Close and delete a new Workbooks

wb.Close

VBA.Kill "NewWorkbook.xls"

Open existing Workbooks

Set wb = Application.Workbooks.Open("MyBook.xls")

Set wb = Application.Workbooks.Open(ThisWorkbook.Path & "\MyBook.xls")   'open from the same existing Workbooks

Close a Workbooks

ThisWorkbook.Close True  'True indicate savechange Yes

ThisWorkbook.Close False  'True indicate savechange No

ThisWorkbook.Close True, "Copy of " & ThisWorkbook.Name ' Save as others

Workbooks.Close   'Close All

Conditional Statements

The use of conditional statement elements of programming is making decisions based on inputs. Below are type of conditional statements uses in VBA.

The If statement (single line)
If  x < 10 then Then MsgBox "The value of x is less than 10"
The If statement (multiple line)
If  x < 10 then Then
     Range ("A1")  =  x
     MsgBox "The value of x is less than 10"
End If

The If with elseif statement
Multiple ElseIf statements within an If block as shown by the following:

If condition Then
   ' Do something
ElseIf condition Then
   ' Do something
ElseIf condition Then
   ' Do something
Else
  ' Do something
End If

The Select Case statement
The following Select Case statement compares the current time against a list of literal times to determine which message to display:

Dim str As String
Select Case Time
   Case Is > #10:00:00 PM#
     str = "Bed time!"
   Case Is > #7:00:00 PM#
     str = "Time to relax."
   Case Is > #1:00:00 PM#
     str = "Work time."
   Case Is > #12:00:00 PM#
     str = "Lunch time!"
   Case Else
     str = "Too early!"
End Select
MsgBox str

Select statements are evaluated from the top to down. Select exits when program find the match.

The Switch statement
The Switch statement is similar to Select, but rather than executing statements, Switch returns a value based on different conditions. The following code is equivalent to the preceding example, except it uses Switch rather than Select:

Dim str As String
str = Switch(Time > #10:00:00 PM#, "Bed time!", _
            Time > #7:00:00 PM#, "Time to relax.", _
            Time > #1:00:00 PM#, "Work time.", _
            Time > #12:00:00 PM#, "Lunch time!", _
            Time >= #12:00:00 AM#, "Too early!")
MsgBox str

Windows handling with VBA

Select an Excel Windows from desktop

Windows("WinActivate.xls").Activate

Sheets("Main").Select

Sheets Handling

Delete sheet without “delete” notification

Application.DisplayAlerts = False

Sheets("List").Select

ActiveWindow.SelectedSheets.Delete

Application.DisplayAlerts = True

Add a new sheet
Set NewSheet = Worksheets.Add
NewSheet.Name = "Sheet Name"

Directory Handling with VBA

Reading a directory

Below code will read a directory in C:\ and will list all directory in Sheet1.

Private Sub cmdRun_Click()

Dim result() As String

Dim Program() As String

Dim dirname As String, count As Long

    Const ALLOC_CHUNK = 50

    ReDim result(ALLOC_CHUNK) As String

    ReDim Program(ALLOC_CHUNK) As String

    sDir = "C:\"

    If Right$(sDir, 1) <> "\" Then sDir = sDir & "\"

    dirname = Dir$(sDir & "*.*", vbDirectory Or Attributes)

    Do While Len(dirname)

        If dirname = "." Or dirname = ".." Then         ' Exclude the "." and ".." entries.

        ElseIf (GetAttr(sDir & dirname) And vbDirectory) = 0 Then             ' a regular file.

        Else

            '### This is directory. ###

            count = count + 1

            If count > UBound(result) Then                  ' Resize the result array

                ReDim Preserve result(count + ALLOC_CHUNK) As String

                ReDim Preserve Program(count + ALLOC_CHUNK) As String

            End If

            result(count) = dirname

            Range("A" & count) = result(count)     ' Write to Sheet1

         End If

        dirname = Dir$

        Loop

    ' Trim the result array.

    ReDim Preserve result(count) As String

End

End Sub

File handling with VBA

Open File 1 and write to File 2

Open "C:\File1.txt" For Input As #1

Open "C:\File2.txt" For Append As #2    ‘ or Output

    While Not EOF(1)

    Input #1, CompLine

    Print #2, CompLine

    Wend

Close 2

Close 1

Find a file and delete if exist

If Dir$("C:\temp\Comp_list.txt") = "Comp_list.txt" Then

    Kill "C:\temp\Comp_list.txt"

End If

Display a form once Excel executed

This code will let the designed Form to appear in the screen once the Excel file executed.
To set this go to Project Window and double click at ThisWorkbook. Copy below code and change the form name as you want eg. frmReadDir.Show

Private Sub Workbook_Open()

    On Error GoTo error_message

        frmReadDir.Show

    Exit Sub

error_message:

    response = MsgBox("Error " & Err.Number & " " & Err.Description, vbExclamation)

    End

End Sub

VBA Introduction from Youtube Video.

Installed VBA for Microsoft Excel 2007

If you are using Microsoft Excel 2007, its hurt when by default Microsoft are not included this feature during normal Office 2007 installation. Please follow below step to install it by yourself. You may need the original installation CD to perform this.

How to install VBA for Excel 2007 from your Office CD

1. From the Start button on your bottom left of the screen goto "Control Panel".

2. From "Control Panel" goto "Add or Remove Programs".

3. Select to your "Microsoft Office 2007" and click to "Change" button.

4. Select "Add or Remove Features" and click on "Continue"

5. Installing option windows prompt out and click on "Office Shared Features".

6. Once "Office Shared Features" selected, goto "Visual Basic for Applications" enable and "Continue".

7. Wait until your computer complete installing VBA.

8. VBA is installed and you need to show the "Developer" item on your toolbar. Click on office Toolbar on the upper left corner of your Excel application. See below Image.

9. Then select "Excel Option" as below.

10. Now Excel Option window prompt out and make sure all item on below picture are checked.

11. Now open "Microsoft Excel 2007" and you will notice a new Menu shows "Developer" on the screen. As shown on below is the features for Office 2007 VBA.

Change "Macro Security" level

On "Developer" menu you'll notice a "Macro Security" menu click this to set your security as below.

Understanding VBA Window

View of default VBA Windows or VBE.

Windows version Excel's supports programming  for Visual Basic for Applications (VBA), which is apart of Visual Basic. It allows spreadsheet manipulation that is impossible to do with standard Excel techniques.

Programmers may write code on VBA window known as Visual Basic Editor (VBE). Instead of writing a script, users also may do some Excel recording activity and the recording will be wrote on macro's recorder.

VBE Toolbar

Try to memorize the icon as you will use them to execute and control your code later.

The Project Explorer

Normally, it is located at the top-left of the VBE. Used to navigate on the elements in the programming environment, you may double click on the object to switch. Other than worksheet object, Project explorer also may included with form object or modules:

Watch Window.

Watch window works during execution activity where users may select some parameters to monitor their value keep on every line program executed.

VBE important features for Programming.

Now from the default VBE window go to View and your may see the other Drop-down menu. You may select or deselect the windows by yourself for your understanding about VBE but there are 3 important sub-window that may help us during programming session. Project Explorer, Watch Window and Properties Window.

The use of watch window already being explain before, now select the property window.

When the propery window loaded in your VBE, double click on each items in project explorer. You'll realize once the item selected, the property window also will change. So we can conclude here, property window works to show the user about the object properties during design time process.

Understanding EXCEL Window

Excel’s basic unit is the worksheet, every worksheet divided to 256 columns and 65,536 rows. The intersection of rows and columns is called "cell", so each worksheet contains 256 x 65,536 = 16,777,216 cells. Excel uses the A1 cell to refer to columns, rows, and cells:

• Columns are designated from A to IV.
• Rows are numbered from 1 to 65536.

*cells A1, A2, Z1234, IV65536 and etc is called the cell address.

But this is not I want to explain here, my main thing is to start using VBA application in Excel.

VBA is stand for Visual Basic for Application. If you are using Excel 2003, you're lucky when VBA is included in normal office package but if you are using Excel 2007 you have to do add/ins from your original installation CD. View here for add/ins procedure.

How to start using VBA?
For shortcut you may press ALT-F11 or follow on below picture to open VBA. Remember this is in Microsoft Excel Windows.

Once you complete the task VBA window will appear.