You can find the Excel tips you're looking at Pro-business Excel VBA Programming by entering the keyword in the textbox below and clicking the "Google Search" button.

Thursday, August 23, 2007

Creating Tables and Columns at Run Time

The following code is an example of creating a Table and Columns using ActiveX Data Objects (ADO):

Sub CreateEmployee()

Dim cn As ADODB.Connection
Dim strSQL As String
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=c:\test.mdb;"
cn.Open
strSQL = "CREATE TABLE `Employee` (`Last Name` varchar(30), `First Name` varchar(30), `Age` integer)"
cn.Execute strSQL
cn.Close
End Sub



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Three Main Objects of ADO

Connection Object

This 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 String Attributes:


Provider – The provider name or driver name that ADO will use to access the database.

User ID – When needed, it is used by the provider to establish the proper rights for accessing the database.

Password – To validate the user, the user’s password is needed by the provider.

Integrated Security – Setting this value to SSPI, the driver will use the Windows NT Integrated
Security. User ID and Password are not used when this is done.

Data Source – If you are connecting to an Access database, this should be set to the file and complete path of the file. This should have the machine name when connecting SQL Server.

Initial Catalog – This attribute should have the database name when connecting to SQL Server.


Recordset Object

This object is important because it is used to manipulate databases. It is a memory allocation in a client computer or server that has a set of rows of one table or more of the database. With Recordset, you can add, edit, delete and update records of the actual table in the database.


Command Object

This object has the information about the command to be executed. The command object could have a Query (SQL) command, Table, View or Stored Procedure.



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Creating MS Access Database File using DAO

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.Close

End Sub



DAO 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 an Access-file using ADO, I have researched the internet and I have tried so many times to do it using ADO but I failed. So I stick with DAO to do the trick. If you know a way to do it with ADO, please let me know. It will help a lot of people out there.

In manipulating the database like creating tables and columns, adding, editing and deleting records, I recommend using the ADO instead of the DAO. It is easier. To know more about ADO, please read the topic: Microsoft ActiveX Data Objects (ADO)



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Sunday, August 12, 2007

Mutual Fund Calculator

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 will see a lot of information in the internet.

Situation:
You are working hard and you have a few dollars in excess from your salary. You want to invest this money in a way that you don’t have to check it in a daily basis because you are busy with your job. You want to make sure that this money is invested wisely and you want someone to manage it for you so you can just check on it in a monthly or quarterly basis. Mutual Fund is ideal for this situation. Your objective is to know the amount of money to spare in a monthly basis to obtain your target specific amount of money in a specific time frame. Excel is the tool you need to forecast your Mutual Fund. This tool is useful when you want to buy a house and lot, or a car in the future.

Putting money in Mutual Fund in a regular basis is called Dollar-Cost Averaging. It is a smart investment strategy. This method works in your favor no matter what happens to the market. I will show you a way to forecast Mutual Fund Dollar-Cost Averaging, in case you want to do it.

Solution:
Copy the following screen grab in your Excel worksheet. Cells B3, B4 and B5 are the data entry fields for annual interest rate, number of investment years and the target amount respectively.















Cell B8 has the formula to compute for the amount to put monthly in Mutual Fund. The formula used the PMT function. The formula is as follow:

=-PMT(B3/12, B4*12, 0, B5)

First parameter, Interest Rate from Cell B3, is divided by the number of months per year, 12 months.

Second parameter, Number of Years, from Cell B4, is multiplied by 12 to determine the total number of months.

Third parameter is zero. The parameter is used for other function of PMT.
Fourth parameter has the target amount.


(Please see PMT Function)

Maybe you are thinking that interest rate is not easy to come up with unlike the number of years and target amount. To bring about the correct estimate of interest rate, you need to choose the Mutual Fund that you want to put your money with. Do a research about its performance from the business newspapers and financial websites. Let’s say, you are planning to invest for the next 5 years and your target amount is $500K. Look for the result percentage of the 5-year performance of the Mutual Fund and use that as the basis for your interest rate. Please note that past performance is not a guarantee of future results, however it is a good estimate. If, for example, the interest rate you’ve got is 8.5%, let’s enter the 3 parameters to B3, B4 and B5. The result is you have to spare $6,716.60 per month for 5 years, as shown below:















Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


A Niche Of My Own

As I was doing some research for what kind of information the internet has about Excel and VBA, I have learned that many websites have already been giving the kind of information that I had in mind when I started this weblog. This is a little bit frustrating, nevertheless challenging to come up with a niche of my own. I have decided to narrow down my objectives to provide specific example of business solutions using Excel and/or Excel VBA. In my future articles, I will be posting basic Excel and VBA functions, and some articles will tackle combinations of these functions to develop or solve a certain business or financial solutions, real examples to solve your number-crunching problems.

Business solutions means vital information is needed to be dug up from a haystack of data residing in your DBMS servers. I know that some of you would say that most of these DBMS, like Oracle, have their own report builder and there’s no need for Excel, thus there’s no need for this weblog. It is correct that reports can be done using the DBMS report builder. But for ad-hoc reports that you need to provide at once, I don’t think that you have the time to ask for your IT guys to create one for you immediately especially when officer approval is needed for your new-report request. Excel comes in handy with this kind of situation. As long as you have the resource to have an extracted data file from the DBMS server or you have an authority to extract them yourself, you can create the report that your boss needs and fast.

Knowing to generate information from a raw data is the skill you need to link business and technology.

To all my readers, this Excel VBA weblog is your BUSINESS TOOL. Enjoy!



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


PMT Function

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 Double


Parameters


Rate - 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 balance you want after you've made the final payment.

Due – An optional parameter. Type of payment due date that specifies when payments are due. Valied values are: 0 or omitted means payments are due at the end of the period, 1 means payments are due at the beginning of the period


For examples, please see: Mutual Fund Calculator



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Project Explorer

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 and Class Modules.



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Properties Window

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 time.



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


F1, The Help Key

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 time.



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Microsoft ActiveX Data Objects (ADO)

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 don’t know exactly what Connection String you have to use, you can use the Connection String builder I discussed in Building Database Connection String Programmatically. Once you have your Connection String, you can store it in a variable or you can save it in worksheet cell for future use with your program.

Below is a sample code on how to make connection to the database:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test.mdb;"


If your Connection String is in a cell in a worksheet, for example, it is in Cell A1 of Sheet1, the Connection String in the code is can be replaced with the following:

cn.Open Sheets(“Sheet1”).Cell(1,1)

or,

cn.Open Sheets(“Sheet1”).Range(“A1”)

Once connected to the database, you can now do the data manipulation. You can create your own table and columns. You can add, edit or delete a record to the database.

In my future posting, I will include the following topics to help you in learning ADO in details:
3 Main Objects of ADO

  • What is a Recordset?
  • What is a Cursor?
  • Executing a Command with ADO
  • Retrieving and Manipulating Data with ADO
  • ADO – Open Method
  • ADO – Close Method
  • Updating Database using ADO



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Creating MS Access Database File using DAO

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.Close

End Sub

DAO 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 an Access-file using ADO, I have researched the internet and I have tried so many times to do it using ADO but I failed. So I stick with DAO to do the trick. If you know a way to do it with ADO, please let me know. It will help a lot of people out there.

In manipulating the database like creating tables and columns, adding, editing and deleting records, I recommend using the ADO instead of the DAO. It is easier. To know more about ADO, please read the topic: Microsoft ActiveX Data Objects (ADO)



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Monday, August 6, 2007

Building Database Connection String Programmatically

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 different Excel VBA forum to seek for help. No one has given me the complete answer but while I was reviewing their suggestions, I tried to put them together and came up with the correct solution. Truly forums are very helpful. Below is the sample source code of the database connection string builder that can provide different OLEDB connectivity programmatically:


Solution:


'PROCEDURE TO GET CONNECTION STRING USING DataLinks
Function GetConnectionString() As String
Dim objLink As New MSDASC.DataLinks
Dim strConnectionString As String
strConnectionString = ""
On Error GoTo LinkErr
strConnectionString = objLink.PromptNew
LinkErr:
GetConnectionString = strConnectionString
End Function




'PROCEDURE TO TEST THE FUNCTION ABOVE
Sub Test()
Dim strCN As String
strCN = GetConnectionString
If strCN <> "" Then
MsgBox strCN
End If
End Sub



The heart of the code is the GetConnectionString procedure. It uses the MSDASC.DataLinks object. But before you can use this code you need to load 2 object files from Microsoft. They are:

1.) Microsoft ActiveX Data Objects 2.5 Library (or later version)
2.) Microsoft OLE DB Service Component 1.0 Type Library


See: Referencing Objects

When MSDASC.DataLinks generates a connection string, it stores the value in strConnectionString variable and eventually, it is the value to be returned by the GetConnectionString function.



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Referencing Objects

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 References by clicking Tools>References on the VBA Editor, as shown below:














Once clicked, the Reference Window will appear (please see below) and you will see choices of objects that you can check to be used in your application development. Some of the objects are already checked. Please do not uncheck any of those because it might bring about an unwanted behavior on MS Excel and can corrupt your file.



















Let’s go back to the original situation. To get rid of the missing object error, you have to check the object that your program is looking for from the list of objects references. If the object is not in the list do number 2 below.

2.) In this approach, an object file is needed to be uploaded in the OS memory and must be recorded to the Windows registry. First thing to do is locate the path of the object file and register it by using the “regsvr32” program. The “regsvr32” program is already included in the Windows installation. To register, click Start>Run and enter the following:

Regsvr32 [path][object file]

When object file is registered successfully, do the steps in number 1.








Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Sunday, August 5, 2007

Finding a Value in the Worksheet

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 String
strValue = “excel vba”
Selection.Find(What:=strValue, After:=ActiveCell, _
Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, matchCase:=False, _
SearchFormat:=False).Activate

To store the row location of the found item, use the following syntax:

Dim intRow As Integer
Dim strValueToFind As String
strValue = “excel vba”
intRow = 0
‘You need to zero out the integer variable first so that when the item is not found it remains zero
intRow = Selection.Find(What:=strValue, After:=ActiveCell, _
Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, matchCase:=False, _
SearchFormat:=False).Row
If intRow = 0 then
MsgBox “Item Not Found”
Else
MsgBox “Item is located at Row: “ & intRow
End if

For the column location:

Dim intColumn As Integer
Dim strValueToFind As String
strValue = “excel vba”
intColumn = 0
‘You need to zero out the integer variable first so that when the item is not found it remains zero
intColumn = Selection.Find(What:=strValue, After:=ActiveCell, _
Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, matchCase:=False, _
SearchFormat:=False).Column
If intColumn = 0 then
MsgBox “Item Not Found”
Else
MsgBox “Item is located at Column: “ & intColumn

End if



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Declaration of Variable

Variable Declaration’s general syntax is:

Dim VariableName As DataType

We can place more than one declaration on a line to save space. For instance, the following line
declares three variables:

Dim strFullName As String, dtBirthday As Date, byteMonth As Byte

Declaration 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 String
Dim dtBirthday As Date
Dim byteMonth As Byte
Dim intAge As Integer
Dim boolIsMarried As Boolean
Dim sglHeight As Single
Dim curMoney As Currency
Dim wbkPayables As Workbook
Dim chSales As Chart

VBA treats a variable as Variant whenever it is used without first declaring it. Or data type is not mention when declaring it, as in:

Dim IsMarried

Declaring a variable without a data type is not a good idea because it is a waste of memory. Waste of memory because the variable will use the memory size requirement for a Variant data type which is 16 bytes. Using the example above, if you intend to use the variable as a storage for a Boolean value but fail to declare it, the variable will use 16 bytes instead of 2 bytes. Fourteen (14) bytes are wasted. In a large program that is using hundreds of variant variables, memory waste is significant. Moreover, maintaining a Variant variable involves more overhead compare to specifically declared variable. Using Variant variable will result in bad performance of your program.



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Wednesday, August 1, 2007

User Input using InputBox in Excel VBA

Situation:
You want to get a user input and you want to know if the entered data is a number or not

Solution:
Make a subroutine with “GetInput” name (you can use any name you desire) and inside it use the InputBox function of the Excel VBA programming language. A variable (vInput) should get the entered value and use the function IsNumeric to check if the value is a number or not. The input box looks like this:












Source Code:





Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Adding Comments

Comments are pretty useful to the programmer for a couple of reasons:
1.) It is a reminder of the structure of the programmer.
2.) It is a reminder of pending tasks in a specific part of the program.

To add a comment to your code, put a single quote mark (‘) at the beginning of the sentence. VBA ignores everything that is written in this line.

Example:




Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


How to Run the Code?

So you just typed in your first source code and you want to run it to test how it works. When you press F5 key, Visual Basic runs the code starting from the first line of the procedure in which the cursor is placed. Pressing F5 is equivalent to clicking Run Sub on the Run menu in Visual Basic Editor.



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


Creating a Procedure in Excel VBA

Situation:
You need to create module procedures to start your programming. You want two procedures to display the phrase “Hello World!” on the screen.

Solution:
There are three types of procedures in Excel VBA: Sub, Function and Property. A Sub procedure performs actions but doesn’t return a value; a Function procedure, however, returns a value. The Property procedure is a type of procedure used in Class module. I will only give example of Sub and Function procedures in here.


Source Code:

















Output:




Explanation:
The program consists of two procedures:
1.) Procedure1

“Procedure1” is an example of SUB procedure. The first line “Sub Procedure1()” is the declaration of the procedure. The line “Dim sString As String” is a variable declaration. To know more about variable declaration, please go to topic “Declaration of Variable”.
The next line is what make this procedure interesting, “sString = Procedure2”. The variable sString value is being changed to the value that Procedure2 provides. Below discusses how Procedure2 works. Whatever Procedure2 provides, the next line invoke the MsgBox command to display a message box with the sString value as the message.
2.) Procedure2
“Procedure2” is an example of Function procedure. The first line “Function Procedure2() As String” simply declares the procedure and states that the return value of it is a string value. Inside the procedure, the only line is:
Procedure2 = “Hello World!”
This means that the procedure will return the value “Hello World!” whenever Procedure2 is used within the program. Thus, the line “sString = Procedure2” in the first procedure also means:
sString = “Hello World!”



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


How to Run the Excel VBA Editor?

Situation:
You want to make an Excel VBA program and you don’t know how to run the Excel VBA Editor.

Solution:
On the Excel main menu, click Tools>Macro>Visual Basic Editor or simply press Alt-F11.
















To open a blank code module in the editor, click Module on the Insert menu.





Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.