So, a recent issue I’ve had using VBA to create UserForms to handle data input is data validation. Throughout my UserForm I’ve included a variety of Combo Boxes that contain lists of information the user can select. In most instances I would like the user to have inputted exactly one of the options within the drop down provided with the combo box; VBA has its own property to handle whether Combo Boxes require an exact match – the MatchRequired property. While this can be used, it creates an issue where if a user leaves a combo box unfilled the combo box will say there is a no match and therefore an error – locking the user out of the form until they input something that is a match. My UserForm is used for modifying and inputting requests our team has had, therefore certain Combo Boxes won’t be able to be filled until a request itself has been completed, with fields such as ‘Quality Assured By:’ only being relevant when the work done has been QA’d. In this instance this Match Required error would cause the user to be forced to enter incorrect data to proceed with the form if they’d accidently clicked into this combo box.
To get around this I turned off the MatchRequired property and created my own data validation Subroutine to check if the value held by each combo box matches a value within the combo box’s list. To do this we will need to create an array of arrays and then iterate through those arrays, something that’s fairly straight forward in more robust languages like Python and C# but a bit more finicky in VBA. Below is some example code which we’ll go through step by step.
Upon the enlightening release of the Cambridge Analytica exposé that revealed the company’s underhanded tactics it utilises to influence and manipulate people with a combination of data analytics, espionage, and ‘honeypotting’, I think it’s incredibly important to discuss how the analytical community needs to ensure a commitment to honest and unbiased analytics, and ethically sourced data.Continue reading “Analytics and Ethics”
Continuing the theme of automatically updating charts, we’re going to look at how to make a chart deal with a dynamically changing data range. This is useful for situations where you have multiple measures that each have a different number of independent variables (the variables plotted on our x-axis, which was the month of the year in our last example). If you use the method as lined out in the last tutorial for this you will find that the chart updates automatically, but the number of variables shown on the x-axis will not change. To solve this, we will need to use Named Ranges; OFFSET; and COUNT to produce the ranges that will feed the automatically updating charts.Continue reading “HOW TO… Excel: Create a Dynamic Data Range for a Chart”
Following on from our last series of HOW TO… Excel, we’re going to expand the concepts used to create the dynamic data table and create another sheet for the same workbook that will be the home of an automatically updating chart! Unlike pivot charts, this will be able to update as soon as new data is inputted into the data backsheets, and will be connected to the same ‘linked cell’ as our previous combo box, so the changes will be persistent throughout the workbook. This will allow us to create a consistent and pleasing user experience for whom ever will be using the tool.Continue reading “HOW TO… Excel: Create an Automatically Updating Chart”
I was taking a glance around the Excel tag in the WordPress Reader, when I came across the following blog post about Linked Pictures in Excel.
This was a little bit of a revolution to me; when I made the Nintendo console dashboard I struggled to work out a passable fix for including dynamic pictures in VBA, ultimately deciding to scrap the code and just go with no pictures. Now that I know how to use Linked Pictures with named ranges to allow dynamic updates I could easily go back and update the old Dashboard!Continue reading “Nintendo Console Dashboard – Linked Pictures”
Due to the volatility of the INDIRECT function, it may be necessary to avoid using it when trying to create large dashboards which will be handling and outputting large amounts of data. So, we’ll need an alternative in these cases. One of the ways this can be done is by using the SUMIFS function, and structuring our data to be compatible with the SUMIFS function.Continue reading “HOW TO… Excel: Using SUMIFS to Create a Dynamic Data Table”
Creating a full and cohesive user experience in an Excel based dashboard can be really difficult if you don’t know where to start. Often, the easiest way to approach the situation will be to utilise Pivot Tables and Pivot Charts, which come with useful tools like slicers to dynamically filter the data with a touch of a button. I have a few pet peeves with Pivots however, mainly being that they require data be in a particular layout; that they can be restrictive on the type and layout of the output they produce (although they do provide a good level of options); and when updating or changing data within a spreadsheet Pivoted data does not refresh unless it’s explicitly told to (which can create a jarring experience when handling and presenting larger data sets).Continue reading “HOW TO… Excel: Create a Dynamic Data Table Using INDIRECT and Combo Boxes”