6 Simple Steps To Speed Up Firefox

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 Firefox

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.

Data Validation In Microsoft Excel

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 validation:

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.

Making a Gantt Chart with Excel

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.

Make Windows XP Run Fast

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

Data Source Is Out Of Control

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 their Oracle system. Records would be entered from different countries across Asia Pacific and stored them into one central database server. Weekly, they would extract the records from it and convert it to Comma Separated file or .CSV file. My Macro program would import those contents of that .CSV file to my worksheets and somehow needed to consolidate them for reports and analysis purposes.

At first, everything went smoothly. One day, the project liaison called and told me that there have been errors in almost all the reports. She sent me a copy of the macro file and source data file to check on the problems. As I was checking the reports of the Excel file one-by-one, reports were devastated. I investigated for hours all the macro modules but I hadn’t seen any clue of what happened to them. So, I asked myself; “How come a program in a good running condition suddenly gives incorrect figures in almost all reports?” The answer is actually right under my nose. When I checked the records in details, I saw the null values of all the Date Fields like “Actual Shipment Date”, etc. were changed. Originally, if the null date field was converted to .CSV file, the content was “NULL”. It was changed to dash (“-“) and eventually would give incorrect values in reports. To correct the problem, I spent days to modify the program modules and worksheet cell formulas that are related to all the date fields. Whew! It was tedious.

That was a learning experience for me. I didn’t see that coming and hadn’t prepared for it. Partly, it was my fault because I presumed that the records format was final and didn’t realize that the data-entry program and data-conversion program were not mine and it could be changed anytime.

For that particular reason, I requested the project liaison to collaborate with their Oracle programmer to advice us if records structures were altered.

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.

Number Rounding Off Issue

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% <------TOTAL

The example above looks incorrect when you add all the percentage items as they are. The percentage total would be 99.99% and not 100%. It is even more incorrect if all percentage items are rounded off to the nearest one. See below:

# of items ------------->%
7 -------------------------->58%
1 -------------------------->8%
3 -------------------------->25%
1 -------------------------->8%
---------------------------------------------------
12 -------------------------->99% <------TOTAL


The total became 99%. But when you put the same calculation to Microsoft Excel, it would display a different result. Please create a New Worksheet in MS Excel and enter the following:

Cell A1 = 7
Cell A2 = 1
Cell A3 = 3
Cell A4 =1
Cell A5 =SUM(A1:A4)

Cell B1 = A1/$A$5

Copy formula of B1 to B3-B4 and copy A5 to B5. Format the entire column B to “Percentage” with zero decimal places. When done, you would see the following result:

# of items ------------->%
7 -------------------------->58%
1 -------------------------->8%
3 -------------------------->25%
1 -------------------------->8%
----------------------------------------------------
12 -------------------------->100% <------TOTAL

The result as you have seen is very interesting. Microsoft treated the value of each individual percentage-item as the number of its formula result and not as what is displayed. This is why the total percentage is 100% and not 99%. In general practices, the MS Excel computation is acceptable to most people and they consider the difference negligible. But there are few people who disagree with this kind of result and consider it unlikely and incorrect to present this type of calculation. Truly, the situation is really a matter of personal point of view and consideration of the person going on this kind of situation.

To satisfy those few people who prefer to have each percentage item to add up to exactly 100%, a VBA script is can be created to do the trick. However, before I present to you the solution, I would like to reiterate that the idea is a matter of preference and most likely debatable to most people because they prefer the Microsoft Excel way. It is correct to some people and the rest of them think otherwise.

Run the script below:



Sub ComputePercentage()

Dim i As Integer
Dim x As Integer
Dim Tot As Single
i = 1
'ROUTINE TO GET THE ROW LOCATION OF THE PERCENTAGE TOTAL
'AFTER THE LOOP i HAS THE ROW NUMBER
Do While Not Cells(i, 2).Formula Like "=SUM*"

i = i + 1

Loop
'COMPUTE THE PERCENTAGES
Tot = 0
For x = 1 To i - 1

If x <> Cells(x, 2) = Round(Cells(x, 1) / Cells(i, 1), 4)

Tot = Tot + Cells(x, 2)

Else

Cells(x, 2) = 1 - Tot

End If

Next x

End Sub

By the way, before you run the program, format the Column B to Percentage with 2 decimal places. When you run the program above, it would give you the following result.

# of items ------------->%
7 -------------------------->58.33%
1 -------------------------->8.33%
3 -------------------------->25.00%
1 -------------------------->8.34%
---------------------------------------------------------
12 -------------------------->100% <------TOTAL

Notice that the last percent number is slightly incorrect. When you divide 1 with 12 the quotient is 8.333333… I will say it again, to those people who prefer it this way, they might consider the inaccuracy of the last number as so small as to be neglected.

It is for you to decide (or your client) which one to use.

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

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.

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.

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.

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.

Easiest Way to Learn Excel VBA Programming by Macro Recorder

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 Column A by clicking on the A header.
3.) Press Control-F to display the Find window.
















4.) Enter 1000 in “Find what:” field and click “Find Next” button.
5.) Stop the Macro Recorder by clicking the stop button:















6.) The following source code is automatically generated by Excel:

Source Code:

There are many things that you can learn in using the Macro Recorder. You can learn the different objects that the built-in functionalities of Excel VBA programming language are using.

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.

Blog for MS Excel VBA Programming

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 not claiming that I know everything about Excel VBA. There are still lots of things that I don’t know about it. In my future posting, if you see errors or incomplete codes, please feel free to inform and correct me.

In the future, I will include in this blog links of different VBA websites that I will find in the internet. If you already know existing links about Excel VBA, I will appreciate if you give it to me and I will include it to my list.

With the help of the readers of this blog, they will eventually find myriad of information resources and it will give quickest tricks to get them out the monotonous work of analysis and make macro programming fun and easy.

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.