Crack Rule

If you’ve followed along with some of the programming articles that I’ve published here at MUO, then you know that I have a major love affair with VBA.

It isn’t so much the language that I like – although it is really intuitive – it’s the accessibility. If you’re already a user of Microsoft Office, then you don’t need anything special, but the things that you can accomplish are pretty special.

Some of the bases we’ve already covered include how to use VBA to pass information using the clipboard, how to send emails from Excel, and even how to make an Internet Browser with VBA.
While many readers found those articles to be helpful, in a few cases I heard from some readers that were brand new to programming – they’d never seen a line of code before – and complained that the instructions were still too complicated to follow.

So, for those of you that would really love to be able to write your own VBA application, but have never typed a single line of code before, I’m going to walk you through making your very first computer program. This program isn’t for programming experts or even moderately skilled coders – it’s for the newbie.

Don’t believe me? Are you doubtful that you have the capability to write an actual program? Well, I’m here to prove to you that you can write your own program whenever you want, using VBA. I’m going to take it slow and I’ll do my best to keep it simple. Ready?

First, you’ll need to choose from an existing Office product already installed on your PC – it can be Word, Excel, Powerpoint, Access or any other. In this example, I’m going to use Excel to create my application. The first step is to get access to the VBA tools by going to the toolbar and clicking on “View”, then “Toolbars” and then “Control Toolbox”.

I was on a computer with a slightly older version of Excel, so if you’re using Excel 2007 or 2010, you’ll find the same controls under the “Developer” menu item and then clicking on “Insert”. What you’re looking for is the Command Button control.

Click on it, and draw a command button onto the spreadsheet. In Word or other MS apps, you’ll have to place the button somewhere convenient. The only point of the button is to launch your application. Another approach would be to write a macro that automatically launches your script when you open the Excel file – but that’s beyond the scope of this article. For now, let’s go with a command button.






Make sure the little “Design Mode” selection is “on” – in the picture above it’s the triangle/ruler/pencil icon. Then, double click on the command button you created, and the VBA Project Editor will open.

This is the development area where you’re going to create your new application. The first thing you want to do is make the front screen of your app. To do this, right click on the Project that’s already open – in my case it’s called “VBAProject” which is the default. Then, select “Insert” and “UserForm”.





Your user form is now loaded into your project under the “Forms” folder with the default name of “UserForm1?.

Double click on “Sheet1?. This is the “code” behind the spreadsheet where you created the command button. On the right panel, you should see the “CommandButton1? selected and “CommandButton1_Click” code already there.

This is called a “function”, and it is what runs when you click the command button in the spreadsheet window.

So, what do you want your command button to do when you click on it? You want it to load the User Form that you just created. You do this by typing in a single line, “Load UserForm1?.









Now that you have your program set up to launch the moment you click on the command button you created, it’s time to design your actual application.

First, you’ll want to design what the program will look like. In my case, I want to write a program with text fields and buttons. The program is going to take a bunch of text, and when I click on a button, it’s going to convert all of that plain text into an HTML output file that I can copy into my blog as a perfectly formatted article.

To design the form, right-click on “UserForm1?, and select “View Object”. You’ll see the actual form itself show up on the right side of the screen. You can click on the form and drag the border lines to resize it however you like.






Using the Controls toolbox,  you can add text fields, labels, and command buttons to your form. If you want to really get creative, you’ll also find frames, selection and check boxes, dropdown lists, and much more. These are the basic components of most basic applications out there.

If the toolbox isn’t showing up, you can find it under the toolbar icons in the top editor menu – when you hover over it, you’ll see the word “toolbox” pop up.  Just drag each component that you want into your form and make it look however you like.


As you place each one into your form, pay close attention to the “Properties” box for that item. You want to edit the “(Name)” field to something that makes sense for what you’re using it for, because this name is how your program is going to reference that item. Make it something that’s clear and makes sense.

What you’ve already created would be more than enough for most applications. You can click on buttons and make the text fields interact with the Excel spreadsheet or Word document where you created the app. However, what were doing today is trying to make an application that has some use outside of the Office app. In my case, I want to create an output to a file on my computer. You can read and write to files by adding what’s called a “Reference” to your project.

A reference is sort of an “add-on” that allows you to write extra commands in your program that you otherwise wouldn’t be able to without the reference. You can usually find the list of references under “Tools” in the toolbar and selecting “References”. For file I/O functionality, just scroll down and select on “Microsoft Scripting Runtime”.




In your program, you can access all of the components you created on your form by typing things like textbox1.text, commandbutton1.caption, in order to access information inside that object (like the text in a text field), or to change that information.

In my case, I want the “Create Output” button to pull all text from all of the fields that I created, and then write them to an output file, formatted in a special way.




Clicking on that button, I see the code for the button click event. The code below may look complicated if you’ve never done VBA before, but it’s really not bad if you look at it one line at a time.

To set up file reading and writing once you’ve added the Reference, you can easily set it up by typing the following lines:

Dim fso As New FileSystemObjectDim fnumDim MyFile as StringMyFile = "c:\temp\OutputArticle.txt"fnum = Freefile()

What does this do? Well, it sets up “MyFile” as the path to your output file you want to write to, and it creates “fnum” as the file identification “key” for the code. Finally, you connect these two together by typing “Open MyFile For Output as fnum” – and bam, you’ve got your open connection to write to the file all you want by issuing Print #fnum, text
commands, as shown below.


See the references above to things like txtHeaderImage and txt1stSection?  Those should actually be txtHeaderImage.text, but with VBA you can use shortcuts like that – leaving out .text in the case of things like text fields, and your program will still work.

Once I finished Printing out the text contained in all of the text fields on the main application screen, all I had to do to text was switch back out of “Design” mode, click on the buttons, and then go check out what the output file looks like.

Sure enough, there was my web code, all formatted with the required HTML tags that I defined in my program, and all of the text from those text fields placed where they’re supposed to go.


If you think about it, you can do anything with an app like this. You could create a simple input form for data entry, which then outputs the data to either a CSV file, or directly into the background Excel spreadsheet. You could write an application that reads information out of a text file, formats the information or does calculations, and then loads that data into a spreadsheet.

The possibilities are really limited only by your own imagination when it comes to VBA. You don’t have to purchase an expensive development package like Visual Studio – just open up any MS Office program, switch to the VBA Editor, and you’re an instant programmer.

Did you try to create your own VBA application? Was it as hard as you thought it would be? Are you a regular VBA programmer at work or at home? Share your thoughts and insights about your love for VBA in the comments section below.
Image Credit: Photo of Computer Via Shutterstock