Click here to Skip to main content
Click here to Skip to main content

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

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
    

License

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

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

Comments and Discussions

 
QuestionProblem: SELECT statement is CASE SENSITIVE Pinmemberkehkeh7-Apr-08 15:53 
GeneralASQL.Update Pinmemberlethanhnhan10-Jul-07 13:48 
Questionzip-files are corrupt?! [modified] PinmemberHolger Kloss16-Jan-07 22:52 
AnswerRe: zip-files are corrupt?! PinmemberRafey16-Jan-07 23:25 
Hi Holger, I have uploaded zip files again. Please use WinRAR in case Winzip don't work.
 
Good Day,
Rafey
 
Smile you will look better Smile | :)

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 16 Jan 2007
Article Copyright 2007 by Rafey
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid