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.

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.

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.