Excel - How to open the VBA Editor

in Excel For All8 months ago

XLVBA


History is Fun - honest

In the beginning, there were ledgers.

7000 years ago, someone picked up a slab of clay, then decided to record a list of expenditures, goods received, goods traded and whatever other pieces of information they wanted.

Humans continued to improve on this for millennia, changing from clay tablets to paper, changing the marking tool to ink. But the results were just a static list of figures.

Then there was a spreadsheet

In 1969, LANPAR was the first electronic spreadsheet - but it resided on a mainframe. Then, in 1976, Jobs, Woz and Wayne founded Apple - which led to VisiCalc in 1979. Then IBM released Lotus 123 in '83, bringing spreadsheet tech to those that used DOS. Finally, in 1985, Microsoft Excel emerged onto the market ('87 for full release).

In 1993, Microsoft added something to Excel that changed my world!!!

Visual Basic for Applications (VBA) was born

Don't get me wrong, other vendors had scripting languages that could talk to their spreadsheet software. But you needed a PhD in computing to use them. The appeal of VBA was it's simplicity. (At least to me)

How to open the VBA Editor

The only thing I'd like you to do today is open the VBA editor, add a Module, then write the most famous piece of "Canadian" code.

The easiest way to open the VBA editor is...

  • Open Excel
  • Press <Alt><F11>

image.png

There it is in all it's glory!!!

I will be referring to three sections in the editor. For lack of better terms, I'm going to call these sections the Project pane, the Properties pane and the Code pane.

image.png

NOTE: You can also open the VBA editor from the Developer Tab

How to Add a Module

A VBA module is just a container to write some code.

If you want, you can save the module separately so that you can use it again in other macros. But ... that's for another story.

An Easy way to insert a Module

  • In the VBA Editor, <click> on Insert, then click on <Module>

image.png

You can find other methods to insert a module here.

  • In the Project Explorer, you should now see your Module

image.png

Once you have inserted a Module, I recommend you name it to something useful

As you write more code, you start reusing your Modules. By default, the modules name is "Modulen"

Typically, the Properties pane is directly below the Project pane. If it is missing, press <F4> to show it.

image.png

Then just change the name.

image.png

The change should show up instantaneously.

image.png

Write a little Code

For the first exercise, the code is just going to write "Hello World" to Cell A1 in Excel. The first time that "Hello World" was documented was by the Canadian Brian Kernighan when he worked at Dell Labs.

In the code pane, Copy and Paste this:

Sub helloWorld()
    Cells(1, 1) = "Hello World"
End Sub

Watch the Magic

If you arrange the Excel Spreadsheet next to the VBA Editor, you can watch the spreadsheet when you run your macros.

image.png

In the VBA editor, <click> Run, then <click> "Run Sub/Userform"

image.png

In your Excel Sheet, you will now see "Hello World"

image.png

That's all for now


I hope you find this useful, or that it gives you some ideas for a script of your own.

Sort:  

Nice... It's been a while since I coded using VBA.

All Office apps support VBA, not just Excel. I once had a project that automatized generating Excel, Word and text files that were before filled in manually.

Excellent!

I’ll get to the other Office tools using VBA in future posts. For now, I want to focus on Excel. I think it’s most folks comfort zone.


When it comes to programming. The flexibility is endless.

I have used Excel to call SAP BAPI’s, parse the return, then send to MSSQL. Use SQL to insert, create and update. Then finally pass to an Oracle Agile API.

Your imagination is the limit!!

Congratulations @gikitiki! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

You distributed more than 41000 upvotes. Your next target is to reach 42000 upvotes.

You can view your badges on your Steem Board and compare to others on the Steem Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

Vote for @Steemitboard as a witness to get one more award and increased upvotes!

thanks for the post, yes it is indeed useful. Excel changed my world too and for years I managed without VBA. But now I think I gotta learn it :-)

Coin Marketplace

STEEM 0.23
TRX 0.02
BTC 11440.71
ETH 395.30
SBD 1.06