How You Can Make Your Own Simple App With VBA

Ads by Google

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

Ads by Google

vba application

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.

vba how to

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

vba how to

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

vba how to

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.

how to write 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.

how to write 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”.

how to write 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.

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.

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.

vba application

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

Join live MakeUseOf Groups on Grouvi App Join live Groups on Grouvi
Windows Troubleshooting
Windows Troubleshooting
8 Members
Windows_10
Windows_10
7 Members
Windows Hacks & Customization
Windows Hacks & Customization
21 Members
Best Windows Software
Best Windows Software
13 Members
Ads by Google
Comments (17)
  • Emily

    I discovered VBA by accident, after using Excel for over a decade. It took me a weekend to learn the basics from a book, and a couple of months to start building more complicated programs to automate half of my work! I really wish I had taken coding in college now, as the process feels extremely natural and straightforward to me.

    I definitely missed out on a great career there, as I realise I would have been a natural programmer. It just wasn’t something that girls did when I was in school. I’ve now taught myself HTML, Flash, and started working on Java for fun. By 35 I’m hoping to have collected several different languages that I can play and create things with.

  • Olram

    i will try this Ryan, thanks for sharing. i’ve discovered, if that’s the right word though, VBA last 2009 but I had resurrected my interest on it as i am now using some of the codes on our work. i’ve never written my own code as newbie and i am have copied codes from the web. since they are all working, i was contended of being dependent and lazy. but i really want to learn in simple codes first as my time allows.
    the downside is my degree is not in-line with any programming but now i’m working a lot on pc. :-)

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

  • ElHaj

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

  • Peter James Escobar

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

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

    • 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. :)

Load 10 more
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.