Pro-business Excel VBA Programming

The following code is an example of creating a Table and Columns using ActiveX Data Objects (ADO):Sub CreateEmployee() Dim cn As ADODB.ConnectionDim strSQL As StringSet cn = New ADODB.Connectioncn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _"Data Source=c:\test.mdb;"cn.OpenstrSQL = "CREATE TABLE `Employee` (`Last Name` varchar(30), `First Name` varchar(30), `Age` integer)"cn.Execute strSQLcn.Close End S...

Connection ObjectThis object is the one used to connect to database, which is then used to execute commands against the database or retrieve a Recordset. The object has the ConnectionString property that is used to specify the database you want to connect to. The Open method establishes the database connection. The Close method releases the connection and the memory used by the object.The Connection String can be generated at run time using the DataLinks. Please see the topic Building Database Connection String Programmatically.Connection...

The following code is a procedure to create an Access Database file with the use of DAO component:Sub CreateDB(vF As String) Dim wrkDB As WorkspaceDim dbNew As DatabaseSet wrkDB = CreateWorkspace("",Application.UserName, "")If Dir(vF) <> "" Then Exit SubSet dbNew = wrkDB.CreateDatabase(vF, _dbLangGeneral, dbEncrypt)dbNew.CloseEnd SubDAO is an old Windows Component to manipulate database. The code above is one good reason to use DAO even if ADO is already available with Windows. I am not sure if there is a way to create...

A Mutual Fund is an investment that you put your money in a big pool of money that is invested in different investment instruments in a diversify manner. I am not going to discuss in details about mutual fund. I assume that when you read this article, you are looking for a way to calculate how much you need to invest in a regular basis to attain your financial objectives. But in case you don’t know Mutual Fund, you can google it and you...

This function returns a Double Data-Type specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate. Syntax is as follows:PMT(rate, nper, pv, fv, type) As DoubleParametersRate - A required parameter specifying interest rate per period.NPer - A required parameter specifying total number of payment periods in the annuity.PV – A required parameter specifying present value that a series of payments to be paid in the future is worth now.FV – An optional parameter specifying future value or cash...

In the Visual Basic Editor (VBE), the Project Explorer is located at the upper-left corner of the client area. Below shows a screen grab of this window: The Project Explorer is a tree structure that has top nodes that represents the currently open Excel VBA Projects. Every Excel Workbook has a corresponding project node that can be explored by clicking its sub-nodes. Each project node has 4 sub-nodes; Objects, Forms, Modules...

The Properties Window displays the properties of an object, in which properties can be managed and modified. Below is the close-up display of the Properties Window: At Design Time, the Properties Window can be used to change some of the properties of the object. But, some properties are read-only and cannot be altered. Some properties can only be changed at design time and some can only be changed at run tim...

Excel has two sets of help group files. When you press the F1 key while you are at the Excel workbook, you will get the “Excel Help” that has the help file for Excel’s workbook functions, formulas, navigation, etc.At the VBA Editor, when you press F1, you will have the “Excel VBA Help”. This help file is very useful when you are programming Excel Macros. It has programming samples and explanation on how to accomplish a certain coding task. I often use this Excel built-in help files and it saves me most of the tim...

Microsoft ActiveX Data Object (ADO) is a Windows 2000 (or later) component for accessing databases. It doesn’t need to be distributed because it belongs to its core services.ADO has the object name “Microsoft ActiveX Data Objects 2.x Library”. The “x” refers to the version number and it varies on every machine. Loading this, please see Referencing Object discussion.The first step to making use of the ADO is connecting to the database. You need to have the correct “Connection String” prior to connecting to the database. If you...

The following code is a procedure to create an Access Database file with the use of DAO component:Sub CreateDB(vF As String) Dim wrkDB As Workspace Dim dbNew As Database Set wrkDB = CreateWorkspace("",Application.UserName, "") If Dir(vF) <> "" Then Exit Sub Set dbNew = wrkDB.CreateDatabase(vF, _ dbLangGeneral, dbEncrypt) dbNew.CloseEnd SubDAO is an old Windows Component to manipulate database. The code above is one good reason to use DAO even if ADO is already available with Windows. I am not sure if there...

Situation:In a few days from now, I will be doing a new Excel VBA project for a more-than-2-year-old client. The new project will require me to produce pivot-table reports from an external data source. This kind of requirement is usually easy because Excel PivotTable already supports external source of data. But the problem with this project, they are requiring me to come up with an Excel VBA program that be used to connect with different database platform. As I was thinking about the solution of this problem, I’ve posted to...

Situation:When you receive an error message about a missing object, sometimes the culprit is not the VBA program itself. Most of the time, this kind of situation is due to improper referencing of the MS Excel to required object file. To correct this problem, there are two things that you can do:Solutions:1.) The required object is already loaded in the OS memory but Excel referencing is not established. To do that, you have to run the VBAProjects...

This is the code to find a value in a worksheet or selected group of cells. This code can only highlight the value when found.Dim strValueToFind As StringstrValue = “excel vba”Selection.Find(What:=strValue, After:=ActiveCell, _Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _SearchDirection:=xlNext, matchCase:=False, _SearchFormat:=False).ActivateTo store the row location of the found item, use the following syntax:Dim intRow As IntegerDim strValueToFind As StringstrValue = “excel vba”intRow = 0‘You need to zero out...

Variable Declaration’s general syntax is:Dim VariableName As DataTypeWe can place more than one declaration on a line to save space. For instance, the following linedeclares three variables:Dim strFullName As String, dtBirthday As Date, byteMonth As ByteDeclaration of a variable is actually defining its data type. Variables are commonly declared with the DIM keyword. Other type of keywords are also used but it will be tackled in my future posting. Examples of variable declaration are as follows:Dim strFullName As StringDim dtBirthday...