We are going to implement the following scenario:
- We are going to get an input from the user.
- We are going to create a list of some random numbers. How many numbers will depend on the number the user provided.
- We are going to create an excel file.
- Change the active working sheet,
- then populate it with the random numbers from the list we created.
- Save and close the excel file.
- Re-open it.
- Retrieve its values.
- Save the contents to a new list.
- Find the sum total.
- Remove all duplicates.
- Find the current sum total.
- Find the minimum and maximum values
- Display them as a dialog to the user.
In this tutorial we are going to utilize Functions, the Display module, the use of labels and we will see an example of string interpolation and error handling in Robin.
As always we begin by declaring our global variables:
We begin with presenting an input dialog to the user.
There they will enter a numeric value that will define how many numbers are going to be generated.
The input dialog:
Note: if we click Cancel the default value is used.
The script behind the dialog:
Let’s analyze it…
At the top lies a label.
Think of labels as wormholes.
By utilizing them we can “jump” to any point in our script.
They can be really useful but you have to be very careful when using them because they might make your scripts hard to read thus confusing and hard to maintain.
The input is stored as text value.
So in order to check if it is a numeric value we have to convert it by utilizing the Text.ToNumber module.
In case the input is not in the correct format an exception is thrown.
If we leave it unhandled our script is going to terminate with an error message.
We implement error handling by utilizing the on error command
Inside the on error we can choose what should happen in case an error occurs (e.g. wrong user input).
In our example we call the ThrowErrorMessage function:
If the input is of wrong type the user is presented with an appropriate message and the flow is redirected to the number_input label, asking the user to provide a new input.
After the successful input, the PopulateExcel function is called:
Notice that we pass the variable PlethoraOfNumbers (the user input) as an input to the function.
The PopulateExcel function first creates a list of random numbers and then internally calls another function to create and populate an excel file.
Note: You can utilize/call one or multiple functions inside another function.
The CreateExcelInstance function launches a new excel instance, adds a new worksheet to it, sets it as the working one (we named it sheet2 and placed it after the default one) and after that a loop is utilized to get all the numbers from the “random numbers list” and store it one by one in the excel file.
Note: By using the .Count property of the RandomNumbers list, we get the numbers of its entries.
Remember, counting in Robin starts from 0, so the first position of the list is going to be RandomNumbers and the final one RandomNumbers[RandomNumbers.Count - 1).
The counting in excel on the other hand, starts from 1.
To make it more clear position i in the list is going to be i + 1 in an excel file (1 <=> 1+1 =2).
Another input dialog is presented to the user.
This time to choose the name the excel file is going to have when saved.
Again if Cancel is clicked the default value is used.
Now we go back to the main flow of our script.
The GetResults function is called, which returns four outputs:
Let’s take a look at the complete function:
At the top we initialize the variables we are going to utilize and return as outputs
After that the RetrieveExcelValues is called.
The RetrieveExcelValues opens the appointed excel file (the one we created in the beginning, goes to its 2nd worksheet, and retrieves its values which then stores in the ExcelDataTable variable.
Note: Notice how we get all the values of an Excel file.
Excel files start at position [1,1]. By getting the first free Row/Column we know where the outermost values are stored. In most, if not every, cases data in excel files exist in this range: [1,1] ~ [FirstFreeRow-1, FirstFreeColumn-1]
Back to the caller function (GetResults)…
We create a new empty list (ExtractedExcelDataList) to store the values we retrieved from the excel file.
Then, each value has to be converted to a numeric one since they are stored as text by default.
We can now do numeric calculations.
We are going to calculate the sum total two times.
One with the list as is and one after removing duplicate entries.
The calculation mechanism is common. We can write a function once and call it twice.
Always keep in mind to make you scripts as intelligible, dynamic and maintainable.
The method responsible for the sum calculation is going to need an input (in our case the list) and is going to produce the sum as an output:
We can now call it, first for the sum total of the complete list:
Now let’s remove the duplicate entries and call the function for the sum total again:
Now we must retrieve the minimum/maximum values.
We can achieve that easily by sorting our list (by default sorting is in ascending order).
This way we know that the smallest number will be at the first position and the biggest at the final position of the list:
Finally a dialog is presented to the user with the calculated values:
Notice the use of multiline strings ( ‘’’ ‘’’) as well as string interpolation (% xxxxx %).
By combing those two we can have multiline strings that include the values of variables.
Screenshot(s) of the whole script:
Useful documentation links: