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
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
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
set end user preferences to an Excel sheet named "
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
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.
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"
AMsgForm.Show "You could have consistent messages with _
AMsgForm.ShowErr "& Errors. This is an error message"
AMsgForm.Show "Use AOption library to get/set user preferences."
AMsgForm.Show AOptions.StrGet(OptionE.Option1, "Not found")
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 "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")