Start Using JavaScript to Automate Excel

The Problems with VBA

Visual Basic for Applications (VBA) was created in the 90’s and has practically never changed since. Aside from its absolutely out of date development environment and unique syntax, VBA also poses a huge threat to corporations as a highly effective tool for malware due to its ability to access core operating system processes.

Yes, VBA has its strengths and I personally know people who have made millions of dollars because of quality VBA code. However, there is a faster, safer, and frankly more enjoyable way to automate excel: say hello to the JavaScript Automation Development Environment (JADE) Excel add-in.

What is JADE?

JADE gives you the power to use JavaScript to automate everything within the workbook scope of Excel without making it macro enabled.

Because the JavaScript you write is living in a browser, it does not have access to any operating system features on the host computer. This means that all of your automation code is limited to your workbook, making it extremely safe to share.

JADE offers an environment that allows the developer to write JavaScript directly in the workbook, and it can even pull in code modules from the cloud using GitHub Gists.

A Short Demo

Let’s quickly demonstrate how to use JavaScript in Excel to automate placing a time stamp.

First, you will need to add the add-in to your excel workbook. Instructions on how to do so can be found here.

Once you have the add-in up and running select Add a Code Module

Select “Add a Code Module”

Enter the name of the module; in the below example the name is “Demo”.

Name the module “Demo”

This will create the code editor below. You may want to resize it to fit your screen better.

Simply write, save, and run JavaScript in Excel!

You will notice that write_timestamp is currently shown in the “function name” drop-down selector. Click the “Run” button to execute the function and write the current date and time into the active cell.

You can change the timestamp to text as well!

JADE also comes with handy error messages that tell you the problem and where to fix it. If you were to forget the closing quotation mark, you would see the following:

Running your script also saves it. If you want to save without running, you can click the “Save” button. You can safely close and reopen your workbook. You can even send a copy of your workbook to another person and they can run the code you wrote!

This completes the demo. You can start using JADE for free by getting it from the Microsoft add-in app store.

More Resources

https://medium.com/media/266c80ac2ae5146b6c141d25008ea7ac/href

JADE Support page: https://support.jsvba.com/

Excel JavaScript API Overview: https://docs.microsoft.com/en-us/office/dev/add-ins/reference/overview/excel-add-ins-reference-overview


Start Using JavaScript to Automate Excel was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.

The Problems with VBA

Visual Basic for Applications (VBA) was created in the 90's and has practically never changed since. Aside from its absolutely out of date development environment and unique syntax, VBA also poses a huge threat to corporations as a highly effective tool for malware due to its ability to access core operating system processes.

Yes, VBA has its strengths and I personally know people who have made millions of dollars because of quality VBA code. However, there is a faster, safer, and frankly more enjoyable way to automate excel: say hello to the JavaScript Automation Development Environment (JADE) Excel add-in.

What is JADE?

JADE gives you the power to use JavaScript to automate everything within the workbook scope of Excel without making it macro enabled.

Because the JavaScript you write is living in a browser, it does not have access to any operating system features on the host computer. This means that all of your automation code is limited to your workbook, making it extremely safe to share.

JADE offers an environment that allows the developer to write JavaScript directly in the workbook, and it can even pull in code modules from the cloud using GitHub Gists.

A Short Demo

Let’s quickly demonstrate how to use JavaScript in Excel to automate placing a time stamp.

First, you will need to add the add-in to your excel workbook. Instructions on how to do so can be found here.

Once you have the add-in up and running select Add a Code Module

Select “Add a Code Module”

Enter the name of the module; in the below example the name is “Demo”.

Name the module “Demo”

This will create the code editor below. You may want to resize it to fit your screen better.

Simply write, save, and run JavaScript in Excel!

You will notice that write_timestamp is currently shown in the “function name” drop-down selector. Click the “Run” button to execute the function and write the current date and time into the active cell.

You can change the timestamp to text as well!

JADE also comes with handy error messages that tell you the problem and where to fix it. If you were to forget the closing quotation mark, you would see the following:

Running your script also saves it. If you want to save without running, you can click the “Save” button. You can safely close and reopen your workbook. You can even send a copy of your workbook to another person and they can run the code you wrote!

This completes the demo. You can start using JADE for free by getting it from the Microsoft add-in app store.

More Resources

JADE Support page: https://support.jsvba.com/

Excel JavaScript API Overview: https://docs.microsoft.com/en-us/office/dev/add-ins/reference/overview/excel-add-ins-reference-overview


Start Using JavaScript to Automate Excel was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.


Print Share Comment Cite Upload Translate
APA
Elijah A Allen | Sciencx (2024-03-28T22:44:03+00:00) » Start Using JavaScript to Automate Excel. Retrieved from https://www.scien.cx/2022/01/07/start-using-javascript-to-automate-excel/.
MLA
" » Start Using JavaScript to Automate Excel." Elijah A Allen | Sciencx - Friday January 7, 2022, https://www.scien.cx/2022/01/07/start-using-javascript-to-automate-excel/
HARVARD
Elijah A Allen | Sciencx Friday January 7, 2022 » Start Using JavaScript to Automate Excel., viewed 2024-03-28T22:44:03+00:00,<https://www.scien.cx/2022/01/07/start-using-javascript-to-automate-excel/>
VANCOUVER
Elijah A Allen | Sciencx - » Start Using JavaScript to Automate Excel. [Internet]. [Accessed 2024-03-28T22:44:03+00:00]. Available from: https://www.scien.cx/2022/01/07/start-using-javascript-to-automate-excel/
CHICAGO
" » Start Using JavaScript to Automate Excel." Elijah A Allen | Sciencx - Accessed 2024-03-28T22:44:03+00:00. https://www.scien.cx/2022/01/07/start-using-javascript-to-automate-excel/
IEEE
" » Start Using JavaScript to Automate Excel." Elijah A Allen | Sciencx [Online]. Available: https://www.scien.cx/2022/01/07/start-using-javascript-to-automate-excel/. [Accessed: 2024-03-28T22:44:03+00:00]
rf:citation
» Start Using JavaScript to Automate Excel | Elijah A Allen | Sciencx | https://www.scien.cx/2022/01/07/start-using-javascript-to-automate-excel/ | 2024-03-28T22:44:03+00:00
https://github.com/addpipe/simple-recorderjs-demo