How You Can Make Your Own Simple App With VBA

Affiliate Disclosure: By buying the products we recommend, you help keep the lights on at MakeUseOf. Read more.


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 Pass Any Information Between VBA Applications Using The Clipboard Pass Any Information Between VBA Applications Using The Clipboard One of the most frustrating parts of working with VBA inside specific applications, is that it’s not always easy to get two applications to “talk” to each other. You can try for very quick transactions... Read More using the clipboard, how to send emails from Excel How to Send Emails From an Excel Spreadsheet Using VBA Scripts How to Send Emails From an Excel Spreadsheet Using VBA Scripts Our code template will help you set up automated emails from within Excel using Collaboration Data Objects (CDO) and VBA scripts. Read More , and even how to make an Internet Browser with VBA How To Make Your Own Basic Internet Browser Using VBA How To Make Your Own Basic Internet Browser Using VBA When you really stop to think about it, an Internet browser in its simplest form isn't really that impressive an application. I mean, yes, the Internet is amazing by anyone's standards. The concept of linking... Read More .

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

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.

How You Can Make Your Own Simple App With VBA vbaapp9

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

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

Explore more about: App Development, Programming, Visual Basic Programming.

Whatsapp Pinterest

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Vivek singh
    January 22, 2018 at 2:49 am

    Hi i m vivek
    I love vba and i want to become good programmer
    But i don't good knowledge of vba lack of knowledge i don't create application and i want to learn more advance excel+vba so pls
    Help me to become a good programmer
    Contact me on my no +919911282334

  2. Lily
    July 11, 2016 at 9:30 am

    Hello, Ryan. It is a really good tutorial. You explain everything in details. But I just do not see who needs VBA apps. Wiki says it is used on Excel. Can I develop a mobile app with this language? Something like they offer here. I have tried to google some info about this kind of development and found this thread where they day that it is unsafe, it crashes, it is slow, difficult to maintain and so on. Can you name the advantages it has? Because I do not see how it is worth developing such an app after reading all this.

  3. Emily
    March 27, 2015 at 9:29 am

    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.

  4. Olram
    February 26, 2015 at 1:49 pm

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

  5. Ali
    July 2, 2012 at 8:59 am

    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.

  6. ElHaj
    July 2, 2012 at 3:31 am

    just a link, that prove to be useful for me as a vba beginner,

    • M
      October 7, 2017 at 4:56 pm

      link not working

  7. Peter James Escobar
    July 1, 2012 at 3:02 am

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

    • Ryan Dube
      July 1, 2012 at 4:23 am

      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
        July 2, 2012 at 10:25 am

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

  8. Humza
    June 30, 2012 at 5:24 pm

    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?

    • Ryan Dube
      July 1, 2012 at 4:22 am

      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.

  9. Ben
    June 30, 2012 at 8:35 am

    Programming is not for the faint-hearted!

  10. John@EconEngineer
    June 30, 2012 at 4:21 am

    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.

    • Ryan Dube
      July 1, 2012 at 4:20 am

      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.

  11. Laga Mahesa
    June 30, 2012 at 3:35 am

    Eewwww vb.

    • Ryan Dube
      July 1, 2012 at 4:19 am

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

      • Laga Mahesa
        July 2, 2012 at 10:21 am

        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.

  12. Luis Gomez
    June 30, 2012 at 12:01 am

    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?

    • Ryan Dube
      July 1, 2012 at 4:18 am

      Hi Luis - yes, actually you're correct. You can use "Freefile" without actually using the FileSystemObject to create the file handler in VBA.