How to use VBA in MS Word or MS Excel with MS Access Database?

Assuming that you have already had a MS Access Database setup and create a query.

1.    Make sure that Microsoft DAO Object Library is active. This can be done by go to Tools > References and check the box. In this case, I’m using Microsoft DAO 3.6 Object Library
2.    Create a sub

Sub test()

End sub

3.    Connect to the database

Dim DB As Database
Set DB = OpenDatabase(“c:\path\db.mdb”)

4.    Query the database (in this case is to call a query from the database

Dim Qd As QueryDef
Dim RS As Recordset
Set Qd As DB.QueryDefs(“myQueryName”)
Set RS = Qd.OpenRecordset()
Qd.Close

5.    Loop through the result

Do While Not RS.EOF
MsgBox RS.Fields(0).Value
RS.MoveNext
Loop
RS.Clode

6.    Passing Parameter

Qd.Parameters(0) = “param”

VBA101: A practical guide for VBA user

This post discusses how to write VBA Conditional Statements and how to write VBA Loops

How to write VBA Conditional Statements

If … Else If … Else

If a = b Then
‘ do something
Else If c <> d Then
‘ do another thing
End If

Select Case

Select Case x
Case Is 1
y = 1
Case Is 2
y = 2
Case Else
y = 0
End Select

How to write Various VBA Loops

Do … Loop Until …

Do
‘ do something
Loop Until x < y

Do While … Loop

Do While x < y
‘ do something
Loop

Do While Not … Loop

Do While Not x < y
‘ do something
Loop

For … Next Loop

Dim i As Integer
For i = 1 To 5
‘ do something
Next i

For … Next Loop with Step

Dim i As Integer
For i = 10 to 1 Step -1
‘ do something
Next i

For Each … in … Next Loop

For Each x in xList
‘ do something
Next x

Loop and GoTo (NOT RECOMMENDED)

Dim j As Integer
For i = 0 To 5
b:
If (j = 3) Then GoTo a:
j = i
Next i
a:
j = 4
GoTo b:

VBA101: A practical guide for VBA user

This post contains 2 parts: How to declare VBA Variables and How to write VBA “Functions”

How to declare VBA Variables

Normal Variables

Dim myVar As String
myVar = “Hello World”
Dim myObj As Object
Set myObj = anotherObj

Class” Variables

Private x As String
Sub myVar()
x = “Hello World”
End Sub

How to write VBA “Functions”

No parameter, No return

Sub MySub()
‘ do something
End Sub
MySub

Parameter, No return

Sub MySub(x As Integer)
‘ do something
End Sub
MySub myVar

More than one Parameter, No return

Sub MySub(x As Integer, y As Integer)
‘ do something
End Sub
MySub myVar, myAnotherVar

Optional Parameter, No return

Sub MySub(x As Integer, y As Integer, Optional z As Integer)
If IsMissing(z) Then
‘ do something
End If
End Sub
MySub myVar, myAnotherVar
MySub myVar, myAnotherVar, myOptionalVar

No parameter, Return

Function MySub() As String
‘ MySub is automatically returned
End Function
Str = MySub

Pass By Values/By Reference

Sub MySub(ByVal x As Integer, ByRef y As Integer)
‘ do something
End Sub
MySub myVar, myAnotherVar

VBA101: A practical guide for VBA user

I wrote this guide a few months ago (September, 2010) and I think it might be something useful for people, who are interested in VBA (Visual Basic for Applications).

Things to know

  1. VBA stands for Visual Basic for Applications
  2. VBA only works with Microsoft Office for Windows
  3. VBA allows user to programmatically use Microsoft Office
  4. Press Alt+F11 to jump to Visual Basic Editor view

VBA can …

  1. Automatically update the document
  2. Connect to database and populate information
  3. Do Forms

How to write a Hello World in VBA

Sub MyHelloWorld()
MsgBox “Hello World”
End Sub

Note about VBA

  1. Comment is ‘
  2. No ; at the end of the line
  3. No {} for block code
  4. Use Dim to declare variable
  5. Assigning values to Primitive types do not require Set