VBA Library To Simplify ADO/Excel Object Model Development

Rafey

4.25/5 (7 votes)

Jan 16, 2007

2 min read

72991

2228

Helper functions to simplify ADO INSERT, UPDATE, SELECT for Excel and common Excel worksheet, workbook, range and cell tasks

Sample Image - Excel_VBA_Library.jpg

Introduction

After the arrival of VSTO, VBA development is almost outdated. However, to support Office 2000, developers still need a library to easily code a VBA based solution. Excel VBA Library could be used in order to use Excel as database tables to INSERT, UPDATE and SELECT. There are also other libraries to simplify Excel VBA developer life a bit. I hope it will be of some help.

List of libraries

ACell: Use this library to obtain row, column or cell address for current row.
AComboBox: Use this library to simplify loading of ComboBox and ListBox.
AConst: Defining your constants at one place could be handy, no matter how small your project is.
ALog: Use this library to write a log of a Excel sheet by the name of "Log". For background processes, this log could be very helpful.
AMsgForm: Use this library instead of MsgBox to provide consistent application name and message formatting in your VBA solutions.
AOptions: Use this library to get/set end user preferences to an Excel sheet named "Option".
ARange: Use this library to obtain valid range string or range object from a given row, column.
ASQL: Use this library to use the Excel sheet as a database table. This library could simplify INSERT, UPDATE, SELECT in an Excel sheet.
AStr: Use this library for string manipulation and formatting.
ATable: Use this library to make use of an Excel worksheet as a database table. Make sure that the first row of the sheet contains column names. This library simplifies table based worksheet tasks.

How To Use Code

Create an Excel sheet.
Open VBA editor by pressing Alt+F11.
Drag Drop to include all the VBA library module files in workbook.

Place buttons on sheet and assign the following macros to each button.

Sub Button1_Click()
    AMsgForm.Show "Use ALog library to write information in Log. _
                    A info Log is written."
    ALog.WriteErr "Use ALog library to write information in Log"

    AMsgForm.Show "Use ALog library to write errors in Log. _
                    An error Log is written."
    ALog.WriteInfo "Use ALog library to write errors in Log"
End Sub

Sub Button3_Click()
    AMsgForm.Show "You could have consistent messages with _
                        AMsgForm library"

    AMsgForm.ShowErr "& Errors. This is an error message"
End Sub

Sub Button5_Click()
    AMsgForm.Show "Use AOption library to get/set user preferences."

    AMsgForm.Show AOptions.StrGet(OptionE.Option1, "Not found")
End Sub

Sub Button7_Click()
    Dim rs

    AMsgForm.Show "Use ASQL library to SELECT, INSERT, UPDATE Excel sheet. _
     Now count of cities will be selected in City sheet."

    Set rs = ASQL.Run("City")

    AMsgForm.Show rs.RecordCount
End Sub

Sub Button8_Click()
    AMsgForm.Show "Use AStr  for string manipulation and formatting."

    AMsgForm.Show AStr.Quote("Quote a string in VBA always ends up _
    in dirty and less maintainable code. But its easy with AStr.Quote")

    AMsgForm.Show AStr.Bracket("To bracket use AStr.Bracket")
End Sub