Click here to Skip to main content
13,191,597 members (61,935 online)
Click here to Skip to main content
Add your own
alternative version


21 bookmarked
Posted 15 Jan 2007

VBA Library To Simplify ADO/Excel Object Model Development

, 15 Jan 2007
Rate this:
Please Sign up or sign in to vote.
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


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


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Web Developer
Pakistan Pakistan
Software engineer developing solutions using Microsoft technologies.

You may also be interested in...


Comments and Discussions

QuestionProblem: SELECT statement is CASE SENSITIVE Pin
kehkeh7-Apr-08 15:53
memberkehkeh7-Apr-08 15:53 

I think your library could be of great use. However, I found that the SELECT statement only returns matches that are CASE-SENSITIVE.

In brief, if I run the following in the code with data as organized in the demo:

Set rs = ASQL.Run("City", "CityName = 'karachi'")
AMsgForm.Show rs.RecordCount

It will return 0.

Can you kindly advise how I can solve this so that it will not return a CASE-SENSITIVE result?

GeneralASQL.Update Pin
lethanhnhan10-Jul-07 13:48
memberlethanhnhan10-Jul-07 13:48 
Questionzip-files are corrupt?! [modified] Pin
Holger Kloss16-Jan-07 22:52
memberHolger Kloss16-Jan-07 22:52 
AnswerRe: zip-files are corrupt?! Pin
Rafey16-Jan-07 23:25
memberRafey16-Jan-07 23:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171017.1 | Last Updated 16 Jan 2007
Article Copyright 2007 by Rafey
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid