Add advanced features
If you already have a basic model created then this article will help you learn how to add VBA macros, automatically save changes to your expert spreadsheet, adjust the processing timeout, and force load erroneous COM add-ins.
Running macros in the expert spreadsheet
It is very common to run macros in the expert spreadsheet after receiving a user’s input data. When you begin the HiveLink configuration process for a spreadsheet the HiveLink system creates various code modules in the VBA project. For this reason you must enable Trust access to the VBA project object model in the Excel options under Trust Center then Macro Settings.
HiveLink creates five code modules and a user form. All of these will remain in the expert spreadsheet, but three of the code modules (with the naming prefix of HiveLink_Expert) will be removed from the user spreadsheet upon generation.
Configuring expert macros
In the HiveLink_Expert_Macros module you will find the following two functions, each of which you can modify to add calls to your own existing macro functionality. They are:
- Sub HiveLink_ProcessJob()
- This function gets called in the expert spreadsheet after injecting user input data when processing a user job.
- Sub HiveLink_AfterPublishJob()
- This function gets called in the expert spreadsheet after extracting the results of the user job.
If you change the name of, or remove these functions then they will not be called but HiveLink will still process your jobs just fine. You can add other code to this module but it is recommended to add them to your own code modules.
Removing your sensitive intellectual property
You may end up generating various user spreadsheets over time and it is important that you remember to remove your sensitive macros each time. When you generate a new user spreadsheet HiveLink automatically removes all code modules that begin with the naming prefix HiveLink_Expert.
The HiveLink_Expert_YourCode module is empty but has been created as an example of a module where you can put your own custom code. For example you might want to rename this to something like HiveLink_Expert_Calculations or HiveLink_ExpertLibrary etc.
HiveLink does not automatically remove any additional project references you may have added. This means that each time you generate a new user spreadsheet you will have to manually remove links to other workbooks, data connection configuration details, workbook add-ins, and references to COM add-ins etc.
Here is an example of a reference to a COM add-in in the VBA project (Tools->References):
It is your responsibility to make sure there is none of your sensitive intellectual property remaining in the user spreadsheet before you distribute it.
The HiveLink_Expert_System module contains all of the functionality necessary for the macros to report errors properly back to the HiveLink Data Inbox. If an error occurs in one of your macros that is caught by the error handling goto statements then the macros will report the details of the error when you click on the job details in the Data Inbox. If you remove this functionality then you won’t have that error reporting in the Data Inbox and your job will have to wait until the timeout period is up before it fails from a macro error.
This module also contains a function that provides you the email address of the user who submitted the job. The function is called HiveLink_Expert_GetJobUserEmail. This function is guaranteed to give you the correct email address of the user, which is authenticated by the HiveLink system and guaranteed to be unique and valid. You can call this function from your own macros in your own code modules.
We recommend not changing any of the code in this module.
Running macros in the user spreadsheet
HiveLink will attempt to call certain macros in the user spreadsheet on various different events. You may want to add your own functionality to be called on these events.
The HiveLink_Macros module contains the following three macros:
- Function HiveLink_ValidateJobSubmission() As Boolean
- This function gets called in the user spreadsheet when a user clicks to process their input data. It is a boolean function and therefore returns a value. If this function returns true then HiveLink will submit the user’s input data, whereas if it returns false it will abort submission of the user’s input data. By default this function returns false. You may want to use this function to add validation of various data fields, to give the user a message or maybe to ask for confirmation before submitting etc.
- Sub HiveLink_AfterJobSubmission()
- This function gets called in the user spreadsheet after receiving confirmation that input data has been submitted successfully. This will be called in the background if the progress bar is still active.
- Sub HiveLink_AfterShowJobResults()
- This function gets called in the user spreadsheet after a user gets the result data for a job. You may want to use this function to change the formatting of certain cells depending on the results, to display a message to the user or perhaps to run some other macro functionality you have.
If you choose to add a Process Data button to your spreadsheet, to make it easier for your users, then HiveLink will also add the following function:
- Sub HiveLink_ProcessDataClick()
- This function gets called in the user spreadsheet when the user clicks the Process Data button from a sheet.
If you rename or delete these functions then they will not be called but HiveLink will continue to run just fine.
Other information about macros
HiveLink cannot run macros that are defined in the ThisWorkbook module, sheet modules, or class modules – it can only call macros defined in normal code modules.
It is important to test your macros in both the expert spreadsheet with a real job, and in the user spreadsheet. Ideally you would test the user spreadsheet on another computer to make sure it loads properly and doesn’t require any environment-specific configuration.
If you have any problems with your macros you might want to check out our section on Troubleshooting.
Saving changes to your expert spreadsheet after each job
If you want to make changes to your expert spreadsheet automatically after each user job then you can tell HiveLink to launch jobs into Excel in edit-mode then save the spreadsheet after processing. You can enable this option in the model configuration window in the HiveLink Data Inbox.
Adjusting the timeout for processing jobs
When HiveLink launches the expert spreadsheet to process a job it allows the job a limited time to complete before it kills the Excel process and attempts to process it again. It will attempt to process a single job three times before reporting the job as a failure.
If your expert spreadsheet takes a long time to process then you may need to adjust the timeout period. You can set this value in the model configuration window in the Data Inbox.
Force loading of specific COM add-ins
When a COM add-ins encounters an error often Excel will disable it from loading next time Excel opens. If your expert spreadsheet requires a COM add-in and that COM add-in happens to get disabled then you can configure the Data Inbox to force the loading of this add-in when it processes a job.
To configure COM add-ins to be force loaded click Choose add-ins to force load… in the model configuration window in the Data Inbox.
Edit your model Invite users