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