How You Can Make Your Own Simple App With VBA

programming   How You Can Make Your Own Simple App With VBAIf 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.

Making Your Own Program With VBA

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?

Creating the Framework of Your Application

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

vbaapp1   How You Can Make Your Own Simple App With VBA

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.

vbaapp2   How You Can Make Your Own Simple App With VBA

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

vbaapp3   How You Can Make Your Own Simple App With VBA

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

vbaapp4   How You Can Make Your Own Simple App With VBA

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.

vbaapp5   How You Can Make Your Own Simple App With VBA

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.

vbaapp61   How You Can Make Your Own Simple App With VBA

 

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.

Adding Cool Functionality

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

vbaapp7   How You Can Make Your Own Simple App With VBA

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.

vbaapp9   How You Can Make Your Own Simple App With VBA

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 FileSystemObject
Dim fnum
Dim MyFile as String
MyFile = "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.
vbaapp8   How You Can Make Your Own Simple App With VBA

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.

vbaapp10   How You Can Make Your Own Simple App With VBA

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

The comments were closed because the article is more than 180 days old.

If you have any questions related to what's mentioned in the article or need help with any computer issue, ask it on MakeUseOf Answers—We and our community will be more than happy to help.

15 Comments -

0 votes

Luis Gomez

Interesting, I always use excel cells for input and then a button to process the information. Question: If I use forms do I have to use the FileSystemObject object to create the file handler, or can I go direct with open file as I do when I use a single button to run a macro?

0 votes

Ryan Dube

Hi Luis – yes, actually you’re correct. You can use “Freefile” without actually using the FileSystemObject to create the file handler in VBA.

0 votes

Laga Mahesa

Eewwww vb.

0 votes

Ryan Dube

Oh come on, it’s not that bad. :-)

0 votes

Laga Mahesa

True. It’s certainly better now than in the early days. However, similar to Flash programmers, people who start on VB have a habit of staying on VB. Then they act all surprised and indignant when their world collapses around them.

Also, call me a code nazi if you want, but it really irks me to no end when I see VB apps for sale.

0 votes

John@EconEngineer

VBA is really the only programming language I can do anything with, and I’m pretty fond of it. It has really helped in my career as I’ve been able to make a variety of spreadsheets that assist with record keeping, data entry, processing data, test records for multiple instruments while keeping a history, … Mostly they are custom and hard to transfer, but I’ve been trying to take the time to make them more portable. So far I’ve written up one of the simpler ones that auto-backups your file to a separate directory and appends the date to that file. It runs this automatically, at most, once a day (depending on how often you open it). I’ll include the link here if self-promotion is allowed.
http://econengineer.wordpress.com/2012/06/01/excel-autosave-backup-macro/

0 votes

Ryan Dube

Cool – thanks for sharing John. I have to agree that VBA is nice, in my opinion, for at least helping folks that are typically non-programmers get accustomed to doing things with code. It is a little more forgiving than doing straight VB programming (or most other programming for that matter), but I love that it has gotten more people to get into programming.

0 votes

Ben

Programming is not for the faint-hearted!

0 votes

Humza

For which platform can VBA be useful? Can it be used to make apps like the ones for android (other than java)? And where is it mostly applied to?

0 votes

Ryan Dube

I would say that it is primarily for use on Windows systems. It isn’t really exclusive to Microsoft apps, as there are a lot of software vendors now that have incorporated VBA into their applications. Anything that uses VBA can typically be linked through code to objects and functions exposed in those other applications – it’s fascinating to start integrating and automating apps that all use VBA as a common programming base.

0 votes

Peter James Escobar

It is so hard to develop an App cause you need to know the flow of the program and the syntaxes

0 votes

Ryan Dube

If you start simple and then work your way through learning more functions, you’ll get the hang of it. It just takes time and practice.

0 votes

Laga Mahesa

It helps to visualize your intended outcome. Write all the needed bits – data tables, etc – then join them together in the necessary logical order for your goal.

After that, code those lines. :)

0 votes

ElHaj

just a link, that prove to be useful for me as a vba beginner,
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=302

0 votes

Ali

Thanks for the post.

Its about time somebody highlighted VBA as a tool for creating lightweight applications. I believe software development is not only about making complex algorithms and glorious applications. It bring so much efficiency in our workings when we can automate tasks which are done day in and day out.

VBA can automate quite a lot of tasks. I use it to import large text files in multiple Excel sheets, to make pivot tables from these sheets to summarize data and present it as a report. I also use it with Access.