Thursday, September 23, 2010

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.

Monday, September 20, 2010

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

Sunday, September 19, 2010

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

Saturday, September 18, 2010

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