Simple EXCEL-VBA Report Generation Guidelines

What are the basic components of an Excel Report Generator?
I like modularisation and customisation while coding. EXCEL-VBA offers entirely different kind of customisation compared to other programming language. In the Project Explorer you can see

Microsoft Excel Objects, Forms, Modules categories.

• You can place your procdure/function in any of the objects. I place modules like this:
  Event Handlers are placed inside ThisWorkBook (usually Cell Double Click, Right Click, WorkBookOpen events and handles by these event handlers)
• Procedures and Functions specific to a particular Sheet are placed inside the Sheets
• Utility procedures/functions used by all objects are placed here.

A typical procedure skeleton is like this

Public Sub GenerateReport
...{declaration of variables}
...{query building}
...{fetching data and populating into sheet}
...{formatting data as per their content (number, date etc)}
...{performing sort, subtotal, filter etc}
End Sub

Then call this procedure from the click event of a command button placed in the sheet.

Prominent advantage of EXCEL-VBA lies in customisation of Queries. You can keep your queries in a worksheet cell. Criteria can be easily composed into a cell using cell formulae. For example consider the following example

SELECT EMPNO, NAME, DEPTNO FROM EMP
WHERE DEPTNO=20

You can type this SQL statement in two cells and design the excel sheet as shown below

In the above sheet, the cell contents are like this
A5 is a single point cell for complete SQL SELECT statment. It is concatenation of below cells
=A6&A7

A6 is just SELECT statement entered as text
SELECT EMPNO, ENAME, DEPTNO FROM EMP

A7 is a formula to compose the criteria for DEPTNO it is entered as a formula
=" WHERE DEPTNO="&C3

Note that the user will enter the required DEPTNO in cell C3.
Now you may think that this will reveal the SQL SELECT statement to the users. If you are concerned about the security of source code, you can type them in a separate sheet and protect it using appropriate security measures. It is available in EXCEL. Those security guidelines will be posted in subsequent posts.

Keep on visiting my blog for better understanding EXCEL-VBA from an expert point of view. I assure you my prompt reply and support email me karthiganesh@yahoo.com