Pro-business Excel VBA Programming

In my other blog Blogging Startup Make Money, I have discussed how I made my Firefox faster than usual. Please bear with me, but I believe that I don't have to re-post it here so I will point you to my other blog site for the tips. It's worth it anyway.By the way, If you are not yet using Firefox, this is your chance to get one for free by clicking the orange Firefox button at the lower part of the right sidebar.Click here for the tips: 6 Simple Steps To Speed Up Firef...

One good thing that Microsoft included in MS Excel is the capability to validate data entered in a cell without macro programming. This saves time. The programmer can concentrate on the business logic of the program and not data validation coding. Here's a video from YouTube that teaches how to do the validatio...

...

Is your Excel and other application running slowly? I came across this very good tips on how to make Windows XP run fast. Watch the video belo...

Have you experienced to create an MS Excel reports wherein the source of data is a .CSV file that is extracted from an Oracle system? If yes, then maybe you have also experienced the situation that I have been through.A few months ago, I closed a contract to develop Purchase Order (PO) performance reports and Sales Order (SO) performance reports with one of my old clients. They looked very simple at first because there was no need for me to create data entry modules for the program. The source of the data would be coming from...

Three days ago, I was facing a difficult situation. A client of mine was asking me to make percentage-items of a report to sum up exactly to 100%. To visualize what I’m talking about, look at the example of figures below:# of items ------------->%7 -------------------------->58.33%1 -------------------------->8.33%3 -------------------------->25%1 -------------------------->8.33%-----------------------------------------------------------12 -------------------------->99.99% <------TOTALThe example above looks...

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

Situation:You want to get a user input and you want to know if the entered data is a number or notSolution: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 Cod...

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

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

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

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

Microsoft Excel has a Macro Recorder that lets you record workbook tasks and can help you to quickly learn macro programming. To run the Macro Recorder, click Tools>Macro>Macro Recorder from the menu. Please figure below.Situation:Regularly, you find yourself searching the value 1,000 in column A together with other tasks and you want a program routine to automate the search.Solution:1.) Run the Macro Recorder.2.) Select the whole...

This blog is all about Microsoft Excel Visual Basic for Application. Excel has been around for more than a decade and many businesses have used it as a tool to do business and financial computation. During my 5 years of Excel macro programming, I have seen companies that have problems in customizing their Excel workbooks and they came to me for consultation. This blog covers knowledge I gained during those years. My objective is to help those who are looking for ideas or sample codes to complete their programming tasks.I am...

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