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>
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.
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>
You can find other methods to insert a module here.
- In the Project Explorer, you should now see your Module
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.
Then just change the name.
The change should show up instantaneously.
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.
In the VBA editor, <click> Run, then <click> "Run Sub/Userform"
In your Excel Sheet, you will now see "Hello World"
That's all for now
I hope you find this useful, or that it gives you some ideas for a script of your own.
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 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
STOPVote 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 :-)