![XLVBA](https://steemitimages.com/0x0/https://cdn.steemitimages.com/DQmSVvLR5W7SiYqcRQSEfAJmSbomcnmVdYPKiCYxBnuQYGG/XLVBA.gif) <hr> <h1> History is Fun - honest </h1> <h2> In the beginning, there were ledgers. </h2> 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. <h2> Then there was a spreadsheet </h2> 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!!! <h2> Visual Basic for Applications (VBA) was born </h2> 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) <h1> How to open the VBA Editor </h1> 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. <h2> The easiest way to open the VBA editor is... </h2> - Open Excel - Press \<Alt\>\<F11\> ![image.png](https://cdn.steemitimages.com/DQmVEP9cDGrJ4nUxoLsYmiGjxjWq4zGFQv7QEsknLXLh3Dg/image.png) <em>There it is in all it's glory!!! </em> 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](https://cdn.steemitimages.com/DQmR8cJtz977wUj5DNiaCnZyUmKh9fU7pougQEccJMxdZrt/image.png) NOTE: You can also open the VBA editor from the [Developer Tab](https://www.techonthenet.com/excel/questions/developer_tab2013.php) <h1> How to Add a Module </h1> 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. <h2> An Easy way to insert a Module </h2> - In the VBA Editor, \<click\> on Insert, then click on \<Module\> ![image.png](https://cdn.steemitimages.com/DQmQR2dAbTbV9xLdYyYp3qZeXpSYkj9QTDmrEX6XxwV6qmn/image.png) <sub>You can find other methods to insert a module [here](https://lmgtfy.com/?q=vba+ways+to+insert+a+module). </sub> - In the Project Explorer, you should now see your Module ![image.png](https://cdn.steemitimages.com/DQmT4tuwXWxDbhtzGpJqEWXYUhHjja1byc7ReT383SSpYAh/image.png) <h2> Once you have inserted a Module, I recommend you name it to something useful </h2> As you write more code, you start reusing your Modules. By default, the modules name is "Module<sub>n</sub>" Typically, the <em>Properties</em> pane is directly below the <em>Project</em> pane. If it is missing, press \<F4\> to show it. ![image.png](https://cdn.steemitimages.com/DQmWRfjVV76hFKn8YUJxnSmXimBqhBtbAPFEUxCPWhvk1gN/image.png) Then just change the name. ![image.png](https://cdn.steemitimages.com/DQmWnQAj6P6AabXTjPynagvoqLTckiKT8Q7QuwYRTzs8tqP/image.png) The change should show up instantaneously. ![image.png](https://cdn.steemitimages.com/DQmWnQ4qTxucXtfAsLuFFChmugTNRnvrwqBu7ua3nr7GjKt/image.png) <h1> Write a little Code </h1> 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](https://en.wikipedia.org/wiki/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 ``` <h1> Watch the Magic </h1> If you arrange the Excel Spreadsheet next to the VBA Editor, you can watch the spreadsheet when you run your macros. ![image.png](https://cdn.steemitimages.com/DQmbQzLGuRv6s52eLovYTBD5KeEv8HTvRfWPwzyRF3iRqFf/image.png) In the VBA editor, \<click\> Run, then \<click\> "Run Sub/Userform" ![image.png](https://cdn.steemitimages.com/DQmerZ1SHbtw5K3fS7xVFd8xT3VxGTFPHXWWGT5XpPstCMB/image.png) In your Excel Sheet, you will now see "Hello World" ![image.png](https://cdn.steemitimages.com/DQmdrrwgbkAD1bNQU1dGa2FjkeAGgwLtsguPG7iVN7N2jmq/image.png) <h1> That's all for now </h1> <hr> I hope you find this useful, or that it gives you some ideas for a script of your own.
post_id | 82,684,878 |
---|---|
author | gikitiki |
permlink | excel-how-to-open-the-vba-editor |
category | hive-102332 |
json_metadata | {"tags":["excel","vba"],"image":["https:\/\/steemitimages.com\/0x0\/https:\/\/cdn.steemitimages.com\/DQmSVvLR5W7SiYqcRQSEfAJmSbomcnmVdYPKiCYxBnuQYGG\/XLVBA.gif","https:\/\/cdn.steemitimages.com\/DQmVEP9cDGrJ4nUxoLsYmiGjxjWq4zGFQv7QEsknLXLh3Dg\/image.png","https:\/\/cdn.steemitimages.com\/DQmR8cJtz977wUj5DNiaCnZyUmKh9fU7pougQEccJMxdZrt\/image.png","https:\/\/cdn.steemitimages.com\/DQmQR2dAbTbV9xLdYyYp3qZeXpSYkj9QTDmrEX6XxwV6qmn\/image.png","https:\/\/cdn.steemitimages.com\/DQmT4tuwXWxDbhtzGpJqEWXYUhHjja1byc7ReT383SSpYAh\/image.png","https:\/\/cdn.steemitimages.com\/DQmWRfjVV76hFKn8YUJxnSmXimBqhBtbAPFEUxCPWhvk1gN\/image.png","https:\/\/cdn.steemitimages.com\/DQmWnQAj6P6AabXTjPynagvoqLTckiKT8Q7QuwYRTzs8tqP\/image.png","https:\/\/cdn.steemitimages.com\/DQmWnQ4qTxucXtfAsLuFFChmugTNRnvrwqBu7ua3nr7GjKt\/image.png","https:\/\/cdn.steemitimages.com\/DQmbQzLGuRv6s52eLovYTBD5KeEv8HTvRfWPwzyRF3iRqFf\/image.png","https:\/\/cdn.steemitimages.com\/DQmerZ1SHbtw5K3fS7xVFd8xT3VxGTFPHXWWGT5XpPstCMB\/image.png","https:\/\/cdn.steemitimages.com\/DQmdrrwgbkAD1bNQU1dGa2FjkeAGgwLtsguPG7iVN7N2jmq\/image.png"],"links":["https:\/\/www.techonthenet.com\/excel\/questions\/developer_tab2013.php","https:\/\/lmgtfy.com\/?q=vba+ways+to+insert+a+module","https:\/\/en.wikipedia.org\/wiki\/Brian_Kernighan"],"app":"steemit\/0.2","format":"markdown"} |
created | 2019-12-19 17:10:45 |
last_update | 2019-12-19 17:10:45 |
depth | 0 |
children | 4 |
net_rshares | 2,657,175,812,571 |
last_payout | 2019-12-26 17:10:45 |
cashout_time | 1969-12-31 23:59:59 |
total_payout_value | 0.277 SBD |
curator_payout_value | 0.276 SBD |
pending_payout_value | 0.000 SBD |
promoted | 0.000 SBD |
body_length | 4,658 |
author_reputation | 8,799,225,435,691 |
root_title | "Excel - How to open the VBA Editor" |
beneficiaries | [] |
max_accepted_payout | 1,000,000.000 SBD |
percent_steem_dollars | 10,000 |
author_curate_reward | "" |
voter | weight | wgt% | rshares | pct | time |
---|---|---|---|---|---|
oaldamster | 0 | 24,985,925,336 | 100% | ||
meesterboom | 0 | 1,118,709,748,452 | 50% | ||
sazbird | 0 | 77,553,955,643 | 25% | ||
wisbeech | 0 | 4,043,030,612 | 50% | ||
bmwrider | 0 | 64,643,360,892 | 100% | ||
paulag | 0 | 1,000,210,460,516 | 100% | ||
techken | 0 | 353,966,599 | 5% | ||
frikitiki | 0 | 1,230,721,193 | 100% | ||
b00m | 0 | 80,970,682,760 | 25% | ||
ronaldoavelino | 0 | 8,589,801,715 | 1% | ||
camillesteemer | 0 | -201,826,742 | -10% | ||
theexcelclub | 0 | 275,890,804,946 | 100% | ||
myotherstuff | 0 | 195,180,649 | 100% | ||
juancho10 | 0 | 0 | 1% | ||
excelguy | 0 | 0 | 100% |
Congratulations @gikitiki! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) : <table><tr><td><img src="https://steemitimages.com/60x70/http://steemitboard.com/@gikitiki/votes.png?201912212221"></td><td>You distributed more than 41000 upvotes. Your next target is to reach 42000 upvotes.</td></tr> </table> <sub>_You can view [your badges on your Steem Board](https://steemitboard.com/@gikitiki) and compare to others on the [Steem Ranking](https://steemitboard.com/ranking/index.php?name=gikitiki)_</sub> <sub>_If you no longer want to receive notifications, reply to this comment with the word_ `STOP`</sub> ###### [Vote for @Steemitboard as a witness](https://v2.steemconnect.com/sign/account-witness-vote?witness=steemitboard&approve=1) to get one more award and increased upvotes!
post_id | 82,741,166 |
---|---|
author | steemitboard |
permlink | steemitboard-notify-gikitiki-20191221t230417000z |
category | hive-102332 |
json_metadata | {"image":["https:\/\/steemitboard.com\/img\/notify.png"]} |
created | 2019-12-21 23:04:18 |
last_update | 2019-12-21 23:04:18 |
depth | 1 |
children | 0 |
net_rshares | 4,123,863,044 |
last_payout | 2019-12-28 23:04:18 |
cashout_time | 1969-12-31 23:59:59 |
total_payout_value | 0.000 SBD |
curator_payout_value | 0.000 SBD |
pending_payout_value | 0.000 SBD |
promoted | 0.000 SBD |
body_length | 850 |
author_reputation | 38,705,954,145,809 |
root_title | "Excel - How to open the VBA Editor" |
beneficiaries | [] |
max_accepted_payout | 1,000,000.000 SBD |
percent_steem_dollars | 10,000 |
author_curate_reward | "" |
voter | weight | wgt% | rshares | pct | time |
---|---|---|---|---|---|
gikitiki | 0 | 4,123,863,044 | 10% |
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 :-)
post_id | 82,765,071 |
---|---|
author | theexcelclub |
permlink | q2xn6o |
category | hive-102332 |
json_metadata | {"app":"steemit\/0.2"} |
created | 2019-12-22 21:18:24 |
last_update | 2019-12-22 21:18:24 |
depth | 1 |
children | 0 |
net_rshares | 4,115,802,049 |
last_payout | 2019-12-29 21:18:24 |
cashout_time | 1969-12-31 23:59:59 |
total_payout_value | 0.000 SBD |
curator_payout_value | 0.000 SBD |
pending_payout_value | 0.000 SBD |
promoted | 0.000 SBD |
body_length | 148 |
author_reputation | 40,220,220,244,131 |
root_title | "Excel - How to open the VBA Editor" |
beneficiaries | [] |
max_accepted_payout | 1,000,000.000 SBD |
percent_steem_dollars | 10,000 |
author_curate_reward | "" |
voter | weight | wgt% | rshares | pct | time |
---|---|---|---|---|---|
gikitiki | 0 | 4,115,802,049 | 10% |
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.
post_id | 82,896,708 |
---|---|
author | gadrian |
permlink | q37r0s |
category | hive-102332 |
json_metadata | {"app":"steemit\/0.2"} |
created | 2019-12-28 08:17:18 |
last_update | 2019-12-28 08:19:15 |
depth | 1 |
children | 1 |
net_rshares | 379,516,681,961 |
last_payout | 2020-01-04 08:17:18 |
cashout_time | 1969-12-31 23:59:59 |
total_payout_value | 0.032 SBD |
curator_payout_value | 0.032 SBD |
pending_payout_value | 0.000 SBD |
promoted | 0.000 SBD |
body_length | 209 |
author_reputation | 36,400,815,307,582 |
root_title | "Excel - How to open the VBA Editor" |
beneficiaries | [] |
max_accepted_payout | 1,000,000.000 SBD |
percent_steem_dollars | 10,000 |
author_curate_reward | "" |
voter | weight | wgt% | rshares | pct | time |
---|---|---|---|---|---|
gikitiki | 0 | 8,539,023,165 | 20% | ||
paulag | 0 | 235,579,831,219 | 26% | ||
dustsweeper | 0 | 135,397,827,577 | 22.59% |
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. <hr> 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!!
post_id | 82,913,714 |
---|---|
author | gikitiki |
permlink | q38kbi |
category | hive-102332 |
json_metadata | {"app":"steemit\/0.1"} |
created | 2019-12-28 18:50:06 |
last_update | 2019-12-28 18:50:06 |
depth | 2 |
children | 0 |
net_rshares | 234,360,337,904 |
last_payout | 2020-01-04 18:50:06 |
cashout_time | 1969-12-31 23:59:59 |
total_payout_value | 0.019 SBD |
curator_payout_value | 0.019 SBD |
pending_payout_value | 0.000 SBD |
promoted | 0.000 SBD |
body_length | 409 |
author_reputation | 8,799,225,435,691 |
root_title | "Excel - How to open the VBA Editor" |
beneficiaries | [] |
max_accepted_payout | 1,000,000.000 SBD |
percent_steem_dollars | 10,000 |
author_curate_reward | "" |
voter | weight | wgt% | rshares | pct | time |
---|---|---|---|---|---|
paulag | 0 | 234,360,337,904 | 26% |