Configure your own spreadsheet


 

Overview

The ultimate goal when creating a HiveLink model is to create a spreadsheet you can give to your users so they can access the functionality of your original spreadsheet without being able to access the underlying implementation of that functionality (sensitive macros, data, formulae etc.).

In the end you will have two spreadsheets: the first is your fully loaded expert spreadsheet with all of the calculations and IP (intellectual property); the second is the “lightweightuser spreadsheet that has the same look and feel of the original spreadsheet but with all of the calculations and IP removed.

You will share your lightweight spreadsheet with your users so that they can enter their input data and access your data processing functionality. When they enter their input data and choose to process it HiveLink will send that input data across the internet to your computer, then inject it into your spreadsheet where it will be processed to produce output result data. The output result data will then be extracted from your expert spreadsheet and delivered back to the user’s spreadsheet, seamlessly.

Each time a user submits input data from a lightweight user spreadsheet it creates a data processing job. Each job will be processed by the same spreadsheet on your computer but in isolation from other jobs. Users can only access results from their own jobs.

hivelink-process-overview


 

Spreadsheet configuration

Before you have two different spreadsheets you first have to configure your original spreadsheet so that it can be automatically transformed into the two different versions. In your original spreadsheet you will perform the following general steps to configure it as a HiveLink model:

  1. Define the input tables.
  2. Define the output tables.
  3. Add any macros to run on processing events.
  4. Finalize configuration to end up with the expert and user spreadsheets.

hivelink-configuration-spreadsheets

So, to help you think about the way you define inputs and outputs, let’s look at the configuration design from the perspective of data transfer between the lightweight user spreadsheet and the expert spreadsheet:

hivelink-configuration-inputs

When the user clicks “Process Data” for their input data, the input data is sent from their computer to the same cell locations in your expert spreadsheet.

hivelink-configuration-processing

Next, the input data is processed into output results in your expert spreadsheet. You may optionally define any macros to run after inputs are injected for processing.

hivelink-configuration-outputs

Lastly, the output result values are extracted from your expert spreadsheet and sent back to the user. Any equations or formulae in the output cells will remain private, only the final calculation values in the cells will be sent back to the user.


 

Spreadsheet design

After configuring your inputs, outputs and finalizing your model with a name, the HiveLink service now knows about your model. If you want to make changes to the list of input or output tables then you will have to re-finalize so that the HiveLink service knows about the new version of your model. This helps avoid problems when trying to process jobs with mismatching and incompatible versions.

So, if you make changes to the inputs or outputs in your expert model then those changes need to be added to your lightweight user spreadsheet. Therefore, each time you re-finalize your expert model you have to generate a new user spreadsheet so it receives those new changes.

HiveLink automatically removes code modules from the VBA project if they begin with a special HiveLink naming convention each time you generate a user spreadsheet. This allows you to put your sensitive macros in these modules so you don’t have to worry about removing them each time you create a new user spreadsheet.

The HiveLink system also removes any formulas from the cells in the input and output tables that you defined.

HiveLink does not do any more automatic removal of intellectual property for you. Therefore, it is important that when you generate a user spreadsheet you are careful to remove anything your user doesn’t need to have – Data tables; Workbook links; VBA project references; References to add-ins; Data connections etc.

For this reason, you may want to keep your sensitive calculations and data in separate worksheets, so that each time you generate a new client template it is quick and easy to remove what you want without forgetting anything. It may also be a good idea to save some notes or a checklist to remind you of all the aspects to remove each time.


 

Workbook types

Both the expert and user spreadsheets have to be saved as xlsm macro-enabled workbook types.

For the expert spreadsheet this is because the HiveLink system will inject some macros for you which will be called on various events. For example it will inject a macro to call for processing after injecting the user’s data, allowing you to add your own macro functionality to process user data. If you don’t use macros then you may remove them, but it is not necessary.

For the user spreadsheet macros must be enabled because the HiveLink Excel ribbon plugin gets embedded into the spreadsheet so users can access the HiveLink functionality. When the user opens the spreadsheet it needs macros enabled so HiveLink can load itself. During generation, HiveLink also injects macros into the user spreadsheet, allowing you to add functionality to be run on various events on the user’s side. For example, a macro gets called after the output results are received to allow you to display a message or perhaps change formatting etc. It is best to only leave trivial macro code in the user spreadsheet and keep the sensitive macros protected in your expert spreadsheet.

You may also consider using Microsoft’s VBA project protection to put a basic password protection mechanism on this user spreadsheet code to stop people poking around, but it is quite easy for anyone to break through this with minimal effort so it is best not to rely on that.


 

Have a question? Contact us!

Do you have an idea that you want to try out? Email us and we can help you with the design and strategy for turning your spreadsheet into a HiveLink service!
 


Customize your webpage Create your first HiveLink model