Pro-business Excel VBA Programming

Note: We are not going to discuss here the Consolidation function of Excel. The said function is used if you want to sum up multiple numbers from different Excel files that have same format into a single Excel file. I've got limited time to post to discuss it but will try to tackle it in the near future. In this post, we will combine records from different files with different format. Appending them all in a single Excel file. No summation...

This is the first part of our discussion on how to export data to Excel. We will start with the simplest one; how to transfer data from a flat file (CSV, Tab delimited, PSV, etc.) to Excel. Let's know first what is a flat file...What is a flat file?Wikipedia described it best...A flat file is a file that contains records, and in which each record is specified in a single line. Fields from each record may simply have a fixed width with...

I will give you a very simple Pareto chart example. Create a new workbook and enter exactly the same figures in the screen shot below:The worksheet above is an example of list of reasons why you lost business proposals or quotes to other competitors. Column A has the reason descriptions. Column B has the corresponding number of times with that lost reasons in Column A. Column C has the percentages. Column D is the running percentages of...

The Pareto Chart, also known as the Pareto Analysis, ABC Analysis, or 80-20 Analysis, is based on the principle of "the vital few and trivial many" developed by Vilfredo Pareto, an Italian economist born in 1848. I'm not going to discuss here his life; you can already research on that in Wikipedia or Google. What you will read here is about the importance of his statistical findings, the 80-20 rule, that you can apply in almost every aspect of your life, especially in business. And in business, I will give you an example in...

Here is a simple way to make your Excel worksheet as a simple database. In our example, we will create a vendors table and we will use an Excel data entry form to allow user to populate the vendors table. Here's how to do it...Step 1: Create a new workbook. Rename the first worksheet as "Vendor". It will become the repository of the vendor information that the users will enter to our data entry form.Step 2: At the first row, enter the following...

This is an improvement of my previous post. It always happens to me that whenever I put a password protection in my Excel/VBA program, I need to hide to the regular users not just the confidential data or customized menu, but the password window also should not displayed so that they will not have the idea that the file has a confidential information or has features that they shouldn't see.This is a simple trick that I've been using for years. Open the program that we did in the last post and change code in Open event in the...

There are situations that you need to hide a customized menu or to make worksheets read only from certain users that need your Excel file for informational purposes only and they shouldn't mess around with the important or confidential data in the worksheets.This is the final post of the 4-part article about Excel Password Protection. We'll discuss here how to create a VBA program to make your worksheets and customized menu safe from unauthorized...

I have to apologize that I put off the supposedly next password article for this one. I must discuss this one first because the next password article will need this program module routines. I will be discussing here how to create a customize menu that we can insert in the existing Excel menu. The menu can be used in your program applications in many ways so it is something that you should know when developing Excel programs.So let's get started to create our own customized menu.Step 1: Open you Excel file and run the Visual...

In our 2 previous posts, Excel Password Protection Part 1 & Part 2, we tackled 2 ways to protect your Excel spreadsheets. The first part was about protecting the Excel file itself. The second was about security at the worksheet level.We are now in the third part of Excel Password Protection series. It's time discuss how to give security to your valued source code. Here are the steps:1.) Open your Excel file with the VBA program. Load...

In this second post about Excel Password Protection, we will discuss how to protect individual worksheet of an Excel workbook. There are many reasons why sometimes we need to password protect them but most of them are for keeping the content of the worksheet intact and unmodified, or keeping the confidentiality of the content, e.g. formula for profit margin, item cost, etc. from those who shouldn't be looking at them. Without so much bother,...

One of my readers, his name is Stew, requested me to discuss about Excel password protection because I mentioned it in my last post entitled Advantages And Disadvantages Of Microsoft Excel Database Application. As I said in that post, there are many ways to protect your Excel file. I can not tackle everything in one post, so consider this as the first part of series of articles about Excel password protection. I put off for a while my topic...

Yes, it is possible to make Excel as a database and be able to be a database application just like Access. I am sure that you won't believe me when I say that anything that you can do with a regular programming language is also possible with Microsoft Excel.But of course, there are advantages and disadvantages in using Microsoft Excel as database. What are the advantages?First and foremost, if you have the Microsoft Office in your PC, you don't need anything else to be installed in your computer. You are good to go and can start...

Someone asked me today on how to change the size of radio button's font. In this kind of situation, the first thing that comes to mind is to use the font resizing function for the cells. But, you can not use it to a form control like the radio button.To change the font size of a form control, right click on the radio button and choose "Properties" in the pop-up menu that appears. The properties window will be displayed as shown in the screen...

Scenario:You have a spreadsheet that has amount in US Dollar and you need to convert it to other currency. The conversion should be done automatically whenever the workbook is open. The currency rate should be based on the latest conversion rate. As an example, let's say the conversion would be from US Dollar to Singaporean Dollar. How can we do this?Solution:In this situation, we are going to use a web query to capture the current currency...

I am a little bit off topic with this one but if someone in my office is having this kind of problem then most likely some of the million of users of MS Word around the world are also searching for a way to solve it.Here’s the scenario:You have many pages, let’s say 30, of enumerated items using “Bullets and Numbering” function of Word having two or more sub-bullets in each bulleted items and you want to change the Tab spacing of one of the particular level of all the bulleted items. If you select each and adjust the spacing...

Sometimes in your business application programming using VBA, you'll find yourself in a situation wherein you need to rename a file. Macro Recording at this point will not be helpful as you cannot do the renaming in Excel interactivity. Here are two ways of doing it at a VBA level:Sub DoRename()Name "c:\test.xls" As "c:\test2.xls"End SubAnother way of doing it is by using the CreateObject function:Sub DoRename()Dim fso As ObjectSet fso = CreateObject("Scripting.FileSystemObject")fso.MoveFile "c:\test.xls", "c:\test2.xls"Set...

A few days ago, somebody asked me how to limit the scrolling of a worksheet as soon as he opened his workbook file. He intended to make the first sheet as a Title Page so he should find a way to do it. At first, I thought it was just a simple Excel "setup" workaround at the Tools menu. But after a few minutes of looking around it, I didn't find a way to do the task (or maybe there was one but I didn't find it). So, I ended up making a simple Macro program at the workbook's "Open" event. Here's how I did it:Private Sub Workbook_Open()...