Pro-business Excel VBA Programming

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:

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:

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.

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:

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.

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.

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 need to learn 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, it's objective is to provide myriad of information resources and to give quickest tricks to get you out the monotonous work of creatiung reports and concentrate on analysis, and also to make macro programming fun and easy.

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... It's business intelligence in its own way.

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