Excel (Mac)

Build Modzy integrations in Excel for Mac with VBA

Overview

Microsoft Excel is a workhorse in the business world. Because of its support for the VBA programming language, it's often used as a database, reporting tool, scripting software, and even as a makeshift robotic process automation (RPA) bot. Due to its ubiquity, the Modzy team created a VBA integration that makes it easy to access and run machine learning models hosted on Modzy, right from within a spreadsheet.

Integration in Action

Our integration code on GitHub includes a sentiment analysis example. Here's a gif showing how you can send text from cell A1 to a sentiment analysis model hosted on Modzy. The model returns a score of the sentiment calculated for this text which you can write to cell B1. (BTW, sentiment is scored from -1 to +1, so a score of 0.1455 is moderately positive).

600

Getting Set Up

:arrow-down: Download VBA files

To get started with our Excel for Mac integration and try out an example using our Sentiment Analysis model, go to Modzy's Excel for Mac Github repo and either clone the repository locally, or just download the Modzy_API.bas and SentimentAnalysisExample.cls files.

:new: Create a new spreadsheet

Open up Excel and create a new blank spreadsheet.

:thumbsup: Enable your Developer Tab

To work with VBA, you'll need to enable your developer tab, which you can do in Excel's "Preferences" section. Hover over the "Excel" tab in your top menu and click on Preferences...

444

From here, you'll want to click on Ribbon & Toolbar

1206

Finally, scroll all the way to the bottom of the screen that appears and click on the Developer tab.

538

:door: Open up the VBA IDE

To open up the VBA IDE, click on the Developer tab (which you just made visible) and then click on the Visual Basic icon on the far left side of the ribbon.

740

:arrow-right: Import VBA Modules

Next, you'll need to import the VBA files you downloaded or cloned from GitHub. In the 'Project - VBA Project' window right click on the 'Modules' folder, select the 'Import File' option, then select Modzy_API.bas (which should be saved somehwere on your local computer)

Then, in the "Project - VBA Project" window right click on Sheet1. From there, click on Import File and finally select SenimentAnalysisExample.cls (which should also be saved somewhere on your local computer).

:recycle: Update Environment Variables

At the top of the Modzy_API module, update the URL of your instance of Modzy, along with the API Key you'll be using to call Modzy. Then go to your instance of Modzy and download an API key.

Const modzyURL As String = "https://trial.app.modzy.com"
Const APIKey As String = "u39fh3jf484hf89HFU9l.298vnLjwifjz08Lnwl82"

:tada: Run an example

Finally, you're ready to run an example! First, add any text you'd like to Cell "A1" on Sheet1 of your spreadsheet. The Sentiment Analysis model used in this example was trained on social media data, so it works best on short, informal sentences and phrases.

The easiest way to run an example is to create a rectangle (like in the example below), right click on it, and then select Assign Macro... to assign it the macro "Sheet1.SentimentAnalysis".

600

You can also run the exampl in the VBA IDE. Double click on the "Sheet1" object then click your mouse somewhere within the Sub SentimentAnalysis() subroutine. Finally, click on the triangular run button at the top of the editor.

Customizing Your Integration

If you have other use cases for using ML models from within Excel, besides sentiment analysis, you can customize the SentimentAnalysis.cls script to meet your needs.