Programming

How You Can Make Your Own Simple App With VBA

Anthony Grant Updated 22-01-2020

Visual Basic for Applications (VBA) is a remarkable language. Built into Microsoft Excel, this language can be used to program apps right inside an Excel worksheet.

Advertisement

It’s easily accessible; you don’t need anything more than a working version of Microsoft Office. This makes it very easy to get started.

We’re going to walk through creating an Excel VBA application. It’s going to be simple but will cover some basics that you can use to build more complicated programs in Excel.

What Can I Do With VBA?

Certainly, there are other programming languages that are widely used to create software. VBA remains popular thanks to the widespread use of Excel and how easy it is to get going (you just need Excel to get started).

VBA can perform all kinds of tasks like sending emails from an Excel spreadsheet 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 to creating custom macro toolbars How to Build a Custom Excel Toolbar of VBA Macros Ready to supercharge your Microsoft Excel productivity? A custom toolbar can do just that. We'll show you how to put all your Excel macros within reach. Read More .

How to Make Your Own VBA Application

The VBA application you’re going to make is a simple data entry style form that will take some input and make an output for you. You’ll write VBA code to do some processing to the input, just like programmed software.

Advertisement

The program is going to take a bunch of text and convert it into an HTML output file that can be copied into a blog.

If you want a rundown of the language before writing an app, consider a beginner’s tutorial on writing VBA macros in Excel A Beginner's Tutorial on Writing VBA Macros in Excel (And Why You Should Learn) If you use Excel regularly, it's worth learning how to create VBA macros and get access to many more functions and capabilities. Read More . Let’s get started!

Creating the Application Framework

First, choose an Office product installed on your PC. It can be Word, Excel, Powerpoint, Access or any other.

In this example, we will use Excel to create the application. VBA is widely used with Excel because of the added power it gives spreadsheets. To get started you’re going to need to create a button to trigger your code.

Advertisement

Developer Tab for Excel VBA Toolbar

If you’re using Excel 2007 or later, you’ll find these controls on the menu under Developer > Insert. Find the Command Button control (under ActiveX Controls), and you’ll be ready to roll.

If you can’t see the menu option, here’s how to add the Developer tab to Excel How to Add the Developer Tab to the Ribbon in Microsoft Word and Excel Let's see how you can add the Developer tab to the Ribbon in Microsoft Word and Microsoft Excel and open advanced features. Read More . This is pretty simple and you only need to do it once.

Click on it and draw a command button onto the spreadsheet. This button will launch your application.

Advertisement

Another approach would be to write a macro that automatically launches your script when you open the Excel file, but that’s a little more advanced. For now, let’s use a command button.

Excel Command Button for VBA

Make sure the Design Mode selection is on—in the picture above it’s the triangle/ruler/pencil icon. Double click on the command button you created and the VBA Project Editor will open.

This is the development area where you will 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 this case, it’s called VBAProject which is the default. Then, select Insert and UserForm.

Advertisement

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 where you write the code that will run when you click on the command button.

On the right panel, you should see the CommandButton1 selected and CommandButton1_Click code already there. This is called a function. VBA functions house VBA code. Functions are critically important to programming languages and VBA is no exception.

Excel VBA Code for Command Button UserForm

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.

Excel VBA Load UserForm Macro

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 the application.

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

Excel UserForm for VBA Code

Using the Controls toolbox you can add text fields, labels, and command buttons to your form. These are the basic components of a  VBA application.

You’re going to create a basic layout using some text boxes, and labels.

Excel VBA Userform controls

With all forms the Properties box lets you adjust settings for the form. You want to edit the Caption field to something that makes sense. This name is how your program is going to reference that item, so choose something that’s clear and makes sense.

Adding More Functionality

What you’ve already created would be more than enough for most applications. There are some basic buttons that make the text fields interact with the Excel spreadsheet.

Let’s take it up a notch. It’s time to create the part of the app which will output a file to your computer. You can read and write to files by adding what’s called a “Reference” to your project.

A reference is an “add-on” that allows you to write extra commands in your program.

You can usually find the list of references under Tools in the toolbar by selecting References. For I/O functionality, just scroll down and select Microsoft Scripting Runtime.

Excel VBA References for Macro

Now that the references are there, let’s create a new button. In the toolbar create a new Command Button by simply clicking on the icon. This button will generate the output when clicked.

Change the caption to Create Output so it’s easy to remember what the button does.

Excel VBA Toolbar Macros

Double-clicking on that button, you’ll see the function for the button click event. Let’s add some code to run the output. This code may look complicated, but it’s really not bad once you break it down.

To set up file reading and writing once you’ve added the Reference, use this code:

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. You’ve got your open connection to write to the file by issuing Print #fnum commands.

Excel VBA Code for Userform

These Print commands will print the text you place after it. There is some basic HTML in some of these statements, some others you will notice are simply variables like txt1stSection.

These variables are linked to the text boxes you made in the UserForm.

Printing Output

Head back to the form and fill in all the text fields on the main application screen.

Now switch back out of “Design” mode, click on the output button, and open the file to confirm the results.

Sure enough, there is the web code formatted with the required HTML tags defined in the program. All the text from those text fields is printed and ready to be copied into a blog.

With just these VBA basics you have a lot more you can create.

You could create a simple input form for data entry that outputs the data to either a CSV file. You could also write an application that reads information out of a text file, formats the information, and then loads that data into a spreadsheet.

Doing More With VBA

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 can create apps.

To learn more about VBA there are some great resources to learn Excel macros and tips to avoid some common mistakes writing Excel VBA code.

VBA is not just limited to Windows systems, Mac users can write Excel VBA code Use Macros in Excel on Mac to Save Time and Do More Learn how to boost your spreadsheet productivity with macros in Excel on Mac. Read More as well. There’s no better time than today to learn this established language.

Related topics: App Development, Programming, Visual Basic Programming.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

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. M
    October 7, 2017 at 4:56 pm

    link not working

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

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

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

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

  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.
    http://econengineer.wordpress.com/2012/06/01/excel-autosave-backup-macro/

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