OneDrive as Cache platform In Apps script library with plugins for multiple backend cache platforms I covered a way to get more into cache and property services, and the bmCrusher library came with built in plugins […]
To take the idea covered in Getting lists as a restquery a little further, why not simple expose all the custom blister functions so they can be queried too. This means that you can use exactly the same […]
In Getting lists as a restquery I showed how to get JSON back by querying the scriptDB directly. Here’s how to consume that in VBA. This example is general purpose – it can be used for any […]
In using scriptDB as a noSQL database, I showed how to do queries on a scriptDB as if it were a REST API. Since these blister lists are just data in a scriptDB, we can […]
As I’ve said before you can use Sumproduct() for many things that it probably wasnt designed for. Various array functions could be used to do the same thing, but trying to do it all in sumproduct is […]
All about performance analysis, tips and tricks for getting the best out of both Apps Script and Excel. […]
cprocProfiler.Results (rOutput as range) purpose Reports on this profiling session. arguments rOutput – This is a range representing the first cell (upper left) where the report for this profiler is to be written to and […]
Public Sub Pause(Section As String) purpose Pauses a timing section that has previously been started with the Start method. The main use of the Pause method would be to exclude certain timings, for example the time spent waiting for user […]
cProcProfiler. Start(Section As String, Optional ProcName As String = “Not given”, Optional pLevel As ptLevels = ptLevelLow) purpose Starts a timing section that will last until the Finish or Pause method is executed. The first time it is called for a given section it […]
FinishProfiler purpose Marks the end of a profiling session and stops all timing activity. arguments none returns none For help and more information join our forum, follow the blog or follow me on Twitter
cProcProfiler.Finish(Section As String) purpose Finishes a timing section that has previously been started with the Start method. arguments Section – the name of the Code section. This is the key through which you access this in all […]
cProcProfiler.destroyTimers purpose Clean up when a profiling session is complete and the results have been reported arguments none returns none Notes – Advanced If you are only using one profiler, you will […]
cProcProfiler.StartProfiler(sname As String, Optional pLevel As ptLevels = ptLevelAll) purpose Starts a profiling session that will last until the FinishProfiler method is executed. arguments sName –Purely informational and results will be reported against this name. In more […]
Public Enum ptLevels ptLevelNone ptLevelLow ptLevelMedium ptLevelHigh ptLevelAll purpose These are given when creating a cProcProfiler or starting a cProcTimer. They allow you to filter which sections to include in a profile session without having […]
procProfilerWrapper Normally one cProcProfiler object is enough. A couple of wrapper procedures are provided so you can use the default profiler session without the need to create your own. Public Methods Usage Name Returns Argument(s) […]
When you click on an infobox, use a quicklink or select a category on a generated map the zoom level will change (if you have enabled it in your excel parameters.). Click on an infobox or use a quicklink Here […]
What is the infoBox An infoBox is an enhanced infoWindow. Details can be found here . I will be converting the Google Maps modules on this site to use the infoBox rather than the native Google Maps […]
VizMap: the vba to generate vizMap application This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and the example […]
VizMap: the common vba for setting up parameter sheets This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
Tailoring the look of your VizMap application For this example, I had creates a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) is downloadable from aboveand the example Parameter WorkSheet is called VenuesParameters. […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
This relates to Data Driven Mapping applications […]
VizMap: the Viz Html parameter Block This relates to Data Driven Mapping applications This parameter block is handled by cDataSet classes and is used to define the filename of the to be generated application. It also contains customization […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
If you have a business account for Google Mapping, you’ll need to provide your client id and crypto key. These things are meant to be kept private, so I did not provide a mechanism for […]
Plotting markers on on a Google Earth This article will cover how to create a basic KML file so that your Excel data can be mapped on Google Earth. There is a more advanced implementation […]
This is one of a series of articles about Going serverless with Firebase. You many want to read about Firebase cloud functions and Custom domains and ssl with Firebase hosting before this article. Redirecting requests to your custom domain Now […]
A common Excel task is to categorize values into bands. You can use Frequency or some pivot table capabilities to report on that, but lets say that you want to add a categorization characteristic to […]
VBA references Quite often you need to add some reference to be able to get some code to compile. This is because the code is referencing some external object that the Excel VBA does not […]
Given a start point, a distance and a heading, it is possible to work out the resulting latitude and longitude. See the maths behind it here. See Radians Versus Degrees for the utility functions we […]
A radian is the ratio of an arc’s length to its radius. See the wiki diagram below Radians tend to be used in most trig formulas related to distance calculation, so we are definitely going […]
Here’s an alternative snippet for how to iterate through a collection of files. Lets say we want to look at every workbook in the current directory, except the currently open one, and show each sheet […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
VizMap: the element parameter Block This relates to Data Driven Mapping applications This parameter block is handled by cDataSet classes and defines the dictionary fields required for each tab on a markers infoWindow and how to handle their […]
VizMap: the tabs parameter Block This relates to Data Driven Mapping applications This parameter block is handled by cDataSet classes and defines the dictionary fields required for using as replacements for the standard placemark icon. For this example I […]
VizMap: the measures parameter Block This relates to Data Driven Mapping applications This parameter block is handled by cDataSet classes and defines the dictionary fields required for charting and how to aggregate their contents. For this example I had […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
VizMap: the vba to join master to transactional data This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and […]
Tailoring the look of your VizMap application Info Window look and feel There are a number of things you can tailor to change the behavior and look of your generated application. VizMap Element positioning allows you […]
Overlaying polyLines to make flight paths on google markers This article will cover how to add flight paths to Google Maps. For a quickstart, see Step by Step Guides Try it out first ? If you […]
Navigating using quicklinks and categories Try it out first ? For a summary step by step explanation see How to add quickLinks to a Map If you just want a to try it, then you can […]
cProcTimer Class Normally you do not need to access this class. However, some of the properties can be useful for more advanced applications and rogue use case identification. Public Methods Usage Name Returns Argument(s) Summary […]
cProcProfiler Class Your main interaction with the profiler is through this class. The Usage column indicates the type of profiling session in which you are likely to need to access a particular property or method. […]
Identifying rogue cases – (by that I mean a combination of circumstances that cause a normally well behaved procedure to take forever), is very simple using these profiling tools. For the sake of this example, […]
When I first started playing around with VBA, everything I read emphasized how important it was that when you initialized an object, you remembered to set it to nothing afterwards in order to ‘release the […]
Although I’m not planning to go through a VBA tutorial (there are plenty of them out there far superior than anything I would do), it is clear that a many people do not actually know […]
What can you learn here? Get started Short code snippets Abbreviated explanations Quick examples Get it now This articles on this site are largely about how to do quite big projects. Realizing that you may […]
This tutorial will get you started with Database abstraction with google apps script and show you how to write a small app that uses a sheet as a database. https://docs.google.com/presentation/d/10rEyXjHxo42YV3wRW46o-CgrAZxFqUxyftHPkjLtbt8/embed?authuser=1&hl=en&size=l Open do something in 5 with gas-1-use […]
Quite often Sumproduct is used in cases where a Pivot table just doesn’t cut it. Here’s how to make Sumproduct respect filters in a data table. Pick a column in your data table Lets use […]
This time we are going to use SumProduct and Search together to check for near matches in data. Search() and Sumproduct() Combining Search() and Sumproduct() will give us the option of matching partial strings. This opens up a whole new set of […]
Just like Excel, you can extend Google Sheets by writing custom functions. These functions are written in Google Apps Script. In this section, I”ll show how to do some common VBA things you might want […]
One thing I’ve found you need when developing for Google Apps Script is a timer. Everything is pretty slow ( see Optimizing access to sheet values) so you need to dig into exactly what is good […]
There is a debugger for google apps script. Here’s how to use it Compared to the VBA debugger it sucks, so you probably need to be able to log and assert. javaScript does not have […]
In Understanding Scope for VBA I showed some examples of the visibility of functions and variables across modules and procedures inside a VBA project. If you are moving to Apps Script (or JavaScript) from VBA, you’ll need […]
Creating and working with classes in VBA is very different from working with js classes. In fact, JavaScript is a classless language – there is no such thing as a class. This is not so […]
Put addresses on a map as markers We’ve already looked at how to geoCode addresses using various APIs and how to create maps with Google Maps from Excel data. Now let’s look at using Bing Maps to put […]
We’ve already looked at how to geoCode addresses using various APIs. Now lets look at using Google Maps to put markers on a Map using an Excel worksheet as input. As usual you can find the […]
In a previous section , I explained how to create javascript to create a Google Motion Chart directly from your from Excel data, and optionally embed it in your workbook. Since it is of course […]
Normally I’d want to use the Rest to Excel library to get data into Excel, but sometimes you have to resort to web scraping. My preferred route is to do something in scraperwiki, then use a rest query […]
Named ranges – the opportunities for adaptation and reuse that referring to ranges of data conceptually rather than by cell references really changes your approach to how you use Excel. You immediately start by assuming […]
What can you learn here? basic authentication xmlhttp cBrowser Quick examples get it now Basic Authentication over Http is implemented as an option in the cBrowser class. It took me a while to track this down, but I […]
What can you learn here? Use Google Books API Convert jSon Tailor columns and output Getting data about a book using the ISBN number. Get it now In this section we will look at how […]
The Rest to Excel library is a set of classes and modules that simplify the populating of Excel sheets with data from RestFul APIs. Google Apps Script of course already has plenty of much better ways to […]
In Delegating xml to json conversion to GAS I showed how you could get Google Apps Script to convert XML to JSON by posting the XML and getting back the JSON response – using VBA to illustrate. […]
Interacting with APIs This site uses a lot of calls to Google APIs in order to connect Excel to web applications. There are a number of custom classes used everywhere such as Data Manipulation Classes and Excel […]
What can you learn here? sing cRest Advanced capabilities Building new libraries Accessing the cRest class directly get it now Up till now we’ve been looking at the modules in the Rest to Excel library as the integration […]
What can you learn here? Using cDataSet Using cRest Using cJobject Putting the Rest response on the sheets get it now Now that we have the tools to generate existing or adhoc rest queries, let’s look at how the data […]
The rest excel library uses cdataset to manage the data it retrieves from queries. In Excel, it is often useful to use tables. You can easily create an Excel table from any cDataSet. See How to make a table from […]
JSON data can go many levels down. Whereas the cJobject is designed to mimic javaScript’s capability to deal with this, eventually you will want to populate a two dimensional table in Excel with some data. Consider this […]
On his oUseful blog, Tony Hirst showed how to use Open refine plus various APIs to research which candidates were standing for election in the wards in which they live. This blog has unfortunately disappeared therefore […]
In Getting data out of scraperWiki I showed how to get data into Excel or Google Docs from scraper wiki public data. I thought i may as well start an occasional directory of interesting scrapes I came across. Of course these […]
What is scraper wiki Scraper wiki is an environment which allows you to put some structure around data published in web pages. In addition, it has an sqlLite datastore through which many users of scraperWiki make […]
In Flight data from Fusion I showed how to get a large amount of data from various Google Fusion tables into a javascript app. Since the Fusion API is just a REST API, you can use the Rest […]
What can you learn here? Using cRest Multiple sources Combining Putting together multiple queries on same sheet get it now So far we’ve been considering Rest queries to be discrete. In this section we are going […]
What can you learn here? Using cDataSet Using cRest Using cJobject Doing unusual things with the rest library get it now Having dealt with Rest to Excel library, Adding to the rest library, Adhoc Rest Queries and Populating sheets with Rest […]
What can you learn here? Library of Rest Calls One off queries Argument structure Running one off queries using the cRest class get it now Now that we have a generalized mechanism to easily populate […]
What can you learn here? Library of Rest Calls How to add new entries How to query from data Adding more content to the rest library get it now Now that we have a generalized mechanism […]
What can you learn here? Populate a treeview do REST queries interactively explore the results How to get rest API results into a treeview get it now In Create a treeview from json we looked at how to […]
In javaScript and other languages, creating json representations of an object is a breeze. Something like JSON.stringify (theObject) will do it for you. Aside from liberating data from your application, it makes debugging so much […]
Since this is a ticker collection application we want this to run every now and again. At first I thought about using excel to schedule itself, but too many things generally go wrong with that, […]
Getting data from the bitcoin API The bitcoin public api can provide different types of exchange data for a number of venues. The endpoint for each combination is different, for example these are all valid endpoints […]
Making a ticker dashboard for all venues Next we’ll create a dashboard – most recent ticker data for each venue on one page that looks like this. The dashboard is described in this piece of […]
Setting up the workbook layout and format Here we are going to create a workbook from the JSON manifest. The relevant section is below, and can be found at manifest.child(“setup”). Each type of data is identifed […]
Children As mentioned in the introduction, each cJobject is either a key/value pair or is the parent of some more cJobjects. Accessing the children of a cJobject is straightforward, and as close to the JavaScript as […]
Children shortcuts As covered in How to access children, cJobect children are roughly equivalent to JavaScript objects’ children. However the syntax of VBA means that we have to be a little more verbose than JavaScript. To […]
You’ve probably all seen the Batman plot that can be generated by entering a formula into google Search. Their instant graphing capability turns this complex formula into a scatter-plot of 4 series. Copy this […]
By the time that A gigantic pub crawl gets here, then various components will have suggested pubs inside an area and declared pub crawls. We can use the Maps API to work out the optimal route between […]
Once you’ve analyzed your workbook for columns which are slow to calculate, it may be that the evidence is showing that some of your user defined functions need to be optimized. Or perhaps you have […]
Using textboxes instead of grids. Adding grids such as Flexgrid to VBA userforms is fraught with difficulties to do with versioning, security and so on. Sometimes you have to just bite the bullet and use […]
Creating event handlers dynamically Normally, when creating a control on a form within the development environment, you assign your code for event handling through the control. However, when your controls are created dynamically, clearly you […]
Creating forms dynamically Creating forms and form controls using the IDE is pretty straightforward. When the form is rather static, you can lay it all out in advance. However when the layout of the form […]
Geocode Excel Data with Yahoo Placemaker If you haven’t read about Yahoo rules on doing this, please do. Since we have done this in Google Maps, and in Bing Maps, I thought it would be worthwhile to see […]
There are still many APIS that only respond in XML. Although Excel has XML native it’s still a pain to deal with, and I really wanted to abstract the data format from the processing of […]
This is real interesting mashup using an new Rest to Excel library entry for the urbarama API. This is a repository of interesting architectural interests. The API is quite interesting in that you can you supply a max […]
For this site, I usually create a Google Apps Script version and a VBA version of the projects and libraries discussed. This is not so hard, since there are utility libraries in both languages, so it’s […]
We’ve dealt with parse.com – nosql database for VBA, cParseCom examples and shown the code at parse.com api class for VBA. Here’s a few selected topics to help you understand a lottle more about how […]
This section has moved. Please see the links below. How to use cDataSet Data manipulation Classes in Google Apps Script cDataSet Model
If you want to embed or publish Excel data to web pages, there are many solutions. Excel 2013 does fancy things with skydrive to share data, and there are plenty of activeX plugins and so […]
Filling in the gaps with Mapping APIs This article will cover 2 main topics How to use the most common APIs for adding markers and information layers to Maps How to plot Excel data directly […]
More color schemes The purpose of this app is to select related colors that appear in a predefined scheme (for example pantone matching system etc). That means I’m always looking for more color schemes. If […]
In Color scheme explorer the main color processing scripts are hosted in Google Apps Script. There’s particular no reason to do this, except that it means that I can ensure I use the same algorithms in JavaScript as I do in GAS. Building […]
Deprecated Parse.com has now become parseplatform.org and i will be investigating it at a later stage. I am however leaving the page as if as you may still find it useful. Contact me if you […]
Overlaying shapes on markers Try it out first ? If you just want a to try it, then you can download an example application (googleMapping.xlsm) and come back here later. First you should take a look at Adding markers […]
Following on from Coloring maps and other shapes, here’s a bit of fun that takes the tables of Pantone colors of the year dealt with in Selection of pantone ramps, and applies them to a […]
Alternative schemes showed Ramping color swatches using the Pantone spring 2013 collection colors as the ramp milestones. So far we’ve been laying out a linear ramp. How about a circular one? Luckily How to create a heatmap doughnut […]
When I was writing the Color Fiesta section, I noticed a tweet about something called the Pantone Spring 2013 color collection. I had no idea how pantone worked, so I went off to find out if it […]
Lets say that we want to create a circular color ramp, as used in How to create a heatmap doughnut chart, as in the example shown on the right This is actually 2 steps, using […]
A doughnut chart is sometimes used to show relative category shares, like this. Doughnut (and pie) charts have a bad press. It is generally difficult to see relativity between categories, and there is usually a […]
In Ramping color swatches I showed how you count transition smoothly between colors by manipulation of the red, green and blue components of the source and target colors. I used some dulux paint color names to […]
In Looking up color table I showed how to look up colors from a growing table of color models, in order to create color swatches. These show distinct colors with a hard stop between them, like this […]
Manipulating strings in Excel. As you will know, Excel very quickly runs of out steam as you try to do any kind of string manipulation with cells. Even very basic requirements cause you reach for […]
Here’s how the promise framework is implemented. This is kind of extreme VBA and Excel is very unforgiving. If you are playing around with this, I advise saving often. cDeferred versus cPromise These two classes […]
You can use the Windows API to call setTimer() to do something a little like setTimeout in javaScript. However there are some serious gymnastics to be able to have multiple timers going at the same […]
I posted something on how to do exponential backoff using jQuery promises a while ago. At the time, I never thought I would be implementing that in VBA because of all the asynchroncity and callbacks needed, […]
Here’s where it gets really interesting. Let’s say that we need to get some large number of spreadsheets from Google Docs. We want to do it asynchronously, and we also know that google docs will […]
In Promises in VBA I introduced how to use promises in VBA to orchestrate asynchronous activities. One of the things that you can do in VBA asynchronously is to get data from a workbook or a database […]
Introduction Usually when I write about something here, I’ve either finished it, or at least done a proof of concept. This is just an idea at the moment and if you would like to contribute […]
I first started to think about this problem when I found myself having to tailor workbooks for particular capabilities described on this site. I’m not great a fan of add-ins (for libraries), and I wanted to find a way of making the code openly available, and at the same create a general capability for people to share and update VBA code painlessly, either publicly, or between their own workbooks. […]
From Excel 2003, you could use Tables. A cDataSet is somewhat like a table in that is structured data, expecting headings etc. When you use bigCommit() in cDataSet, it wipes out the existing Excel data […]
Hopefully you will have read How to use cDataSet to see how to abstract data from your sheet and taken a look at the cDataSet Model to see which methods and properties are available. I […]
I use scriptDB a lot , both with Google Apps Script and from other sources, including VBA, and in this blog post, I compare parse.com performance with script DB. One of the things that Parse.com […]
VBA has a garbage collector that runs from time to time in order to free up memory that is no longer being used. in Objects and the garbage collector I showed how to teardown objects so they […]
What can you learn here? Learn about classes Short code snippets Abbreviated explanation You won’t be able to fully leverage an Object Oriented language without knowing how to define objects. A class module defines an […]
You use a class to describe an object. An object is a collection of whatever you like, and encapsulates both the values and references (properties) and actions (methods) associated with that object. You need to […]
Developing a constraint formula to identify naked sets During the development of the sudoku solver it occurred to me that there had to be a better way to look for naked sets than to try a pair, then […]
This problem is often used to show that statistics are sometimes counter intuitive. If you try to imagine how many people you would need to have a 50% chance of two people sharing the same birthday, you’d […]
Summary cHeadingRow is a special type of cDataRow and is used for managing the column headers of a cDataSet You can find the methods and properties documentation on github. And the source code is on Gitbub or below ‘[email protected] […]
Nowadays structured references are the preferred way to access table data in Excel and VBA. Although most of the examples you may have looked at so far have talked about ranges, you can also specify […]
Hadley Wickham has added some data management capabilities to R which are pretty useful for getting data ready for tabulation or visualization. One of these is melt(). Melt can do lots of things, but one that caught my […]
What can you learn here? ramp color scales using in surface charts brighten/darken Creating Charts with colorRamps Create a heatmap in Excel showed how to make charts using the heatmap color ramp. Now that we have […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
What can you learn here? Select worksheets to copy Choose selected columns cDataSets filtering capability Copying partial tables between sheets Get the cDataset.xlm from here By now you are probably familiar with the cDataSets classes. Here […]
How to get data into Excel from your outlook address book get it now Here’s a common problem. You have a list of email addresses in Excel and you want to pick up various pieces […]
Summary cDataColumn is a column of cCell . The rowId argument refers to the row either by row name (where we have enabled labels) or a row number. Where rowId optional and omitted the complete column is operated on. Where rowId is present the the cCell it […]
Sometimes recalculation seems to take a long time and you cant find the formula that is slowing it all down. The advice you find is usually generic, telling you that function X() is slow etc, […]
What can you learn here? Easy format for debugging Remove dollars for fill Shorten addresses This article shows some techniques to manipulate text representations of range addresses. The functions mentioned are all included in the downloadable getting […]
What is this about? If you want to press a button and include all the code you need to analyze your VBA code, this is what you need, and you can be up and profiling […]
What can you learn here? Optimization and profiling filling ranges using VBA using match to partition data Another approach to Array formulas and large Ranges I previously covered a VBA solution for a problem that could […]
What can you learn here? Optimization and profiling Filling ranges using VBA Array formulas I previously covered a VBA solution for a problem that could have been solved using array formulas. However, it really wasn’t the best […]
What can you learn here? array formulas VBA using VBA profiler using worksheet profiler Are Array formulas viable with large ranges On the Excel Blackbelts forum, someone asked a very interesting question about how to analyze relationships between […]
What is bit twiddling Sometimes you need to do many logical operations at once. For example, consider the problem where you want to find which numbers exist in both of two strings, which are stored […]
Let’s take the concept of named ranges a little further. Its true that the TABLE capabilities in later versions of excel give you much more opportunity to organize your data than before, but if we […]
Organizing ranges for reuse – minimizing maintenance of data ranges […]
This is my favorite function – you can do so much with it. What I like about most though, it is that it seems to have been added to excel as a kind of afterthought – its […]
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines