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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 ‘gistThat@mcpher.com […]
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 […]
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 […]
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 […]
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 […]
Automating document ,creating Google Visualization and static tables of VBA projects, serialize instances of VBA classes and generating Google Apps Script skeleton of VBA projects. […]
What to download: All the examples contain all the classes needed for them to work and all projects can be found here. If you just want the main utility classes used throughout this site, in […]
Summary CDataSets is a collection of cDataSet You can find the methods and properties documentation on Github. And the source code is here
Creating heatmaps An easy way to create heatmaps is to use conditional formatting in your spreadsheet. However, we are going to look at creating heatmaps as an Excel Chart, and also to automatically create a […]
The mathematics of color is a complex and interesting topic. Here you’ll find Google Apps Script, VBA and javascript examples of color manipulation including finding the closest match to a given color in a defined color scheme, and the infamous heatmap doughnut chart. There are lots of references to color on this site, and it seems to be a subject second only to jSon in popularity […]
What can you learn here? Get Started with cDataSet Short code snippets Abbreviated explanations Quick examples Download the cDataSet.xlm. This is intended to be a fasttrack to get started with the Data Manipulation Classes that are […]
The cCell class addresses and manipulates a single excel cell and its contents. You can find the methods and properties documentation on Github. The code is also below ‘gistThat@mcpher.com :do not modify this line – see […]
In Using Google Maps I covered how to create various web based interactive maps. Of course many would like to use Excel to create maps, and the topic of chorpleth maps, has been covered many times by […]
What can you learn here? scriptcontrol object make VBArrays make javaScript arrays Array formats In How to use javaScript from VBA I covered how to use the scriptcontrol object to ‘extend’ VBA by scripting in javaScript. […]
What can you learn here? ramp color scales gradient between colors Use a ramp library Generalized Color Ramps If you read A tagCloud in Outlook, How to create a tag cloud or Create a heatmap in Excel you would have […]
There are a few items on this site that cover color ramps and other color manipulation topics. I’ve built up a few snippets on color management in VBA so I thought I should centralize them for easy […]
In Playing around with colors in VBA I introduced the concept of a color table. This is just a list of a name and a matching hex color code in a big excel table. I have been […]
Let’s say you have a color, and you want to find the closest match in defined color scheme set. The first problem is defining what closest means. It’s not a simple problem to solve. There […]
These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available for directly from O’Reilly or Amazon. Many applications have their own names for colors in their palettes. This example […]
I’ve been dong a lot of writing over on Desktop Liberation looking at techniques to run Apps Script/JavaScript code directly from VBA, In this post I’m going to talk about how useful this can be if […]
If you develop shared code for VBA, you’ve probably got some massive workbooks that contain multiple projects that you wish you could separate, but it’s too complicated because they are sharing classes and functions. The […]
Last year, I started to experiment with Google Universal Analytics to measure usage of Excel Apps that people download from Excel Liberation in order to get insight into what’s being used, how it’s being used and […]
Last week, I posted how to instrument Excel workbooks so that usage of them could be tracked using Google Analytics. Now we have a week’s worth of data, let’s see what happened. Instrumentation Recap First […]
Yesterday I showed how to do some data wrangling in VBA and Google Apps Script that Tony Hirst on his oUseful blog had done using scraperwiki, Open refine plus various APIs. Today he posted a quick analysis using […]
I just saw a great post by Tony Hirst on his oUseful blog where he nicely laid out how to use Open refine plus various APIs to research which candidates were standing for election in […]
In documenting VBA procedures automatically I showed how to create web pages that documented your VBA projects. I’ve updated that now to also include a popup showing the detail of function arguments too. This page was automatically […]
In a previous post I covered a way of automatically including and inserting VBA code in Excel from gists. You would normally use this if you wanted to include a bunch of functions and classes […]
Here’s a little bit more on the subject of VBA string manipulation. I showed in a post the other day how things take longer and longer when concatenating large strings in VBA. One reason I […]
I find myself doing a fair bit of VBA at the moment, It was a little adjustment coming back, but I suddenly noticed that string concatenations were taking a long time. For example, this would […]
Figuring out how to serialize native VBA classes to jSon is something I’ve been working on in the background for some time. If you’ve been following this blog or the related Excel Liberation Site, you […]
I came across a great site, builtwith.com, that shows the technology being used in a given web site. You can also take a look and see the usage of various specific technologies over time. Now, […]
In plotting functions in Excel I showed how to create plots from equations without needing to create a data table. Using the same template, here is the Excel version of the well known Batman Plot. Using […]
Today’s entry is pretty cool. I came across the urbarama site, which describes itself as “a collaborative world atlas of architecture and civil infrastructure”. What’s more it has an API, which will return details on […]
In scraping the scraper I showed how to get scraperwiki data into both Excel and Google Apps Script. More interestingly though, I was talking about data for which others had already done the legwork to create […]
Unlike Google Apps Script, which has a fine cloud based library capability, sharing and keeping code current for non-professional developers who use VBA is challenging. The use of add-ins for finished products with specific capabilities […]
I had some questions about authentication from users of the Excel Rest library. Typically Rest queries are authorized through the use of a developer key issued to registered users, but some sites need authorization as […]
In a previous post I covered how to navigate REST responses using a simple treeview control as well as how to create a Treeview from any jSon object in a few lines of code. Recursion […]
If you open an Office Document, you might occassionally find your self with an error that tells you that your “object library is invalid” , “excel has had a catastrophic failure” or you have an […]
In a post the other day i covered how to use the scriptcontrol to call javaScript directly from VBA. Considering that javaScript has plenty of useful methods for manipulating arrays and strings and so on […]
Those of you that read this blog, and the ramblings site, will know that the topic content is about equally split between VBA and javaScript. Until today, I had no idea that there was a […]
I got this idea from Tony Hirst’s great blog. The OpenCorporates API gives rich information many companies. Using Google Refine to reconcile, it cleans up many sources of data to a queryable datasource. So here […]
Color Ramps In a previous post, I showed how to automate the\ creation of a heatmap in Excel. It occurred to me that this could be generalized to any color ramp. Here is an extensible […]
tagCloud in Outlook In a previous example, I showed how to create a tagCloud of various Excel data sources, with a downloadable example on the ramblings web site It occurred to me it would be pretty […]
The tagCloud class Today’s post will demonstrate how to generate tagClouds from various sources in Excel. As an example we will use some tweets retrieved by the rest- Excel library A ‘Mitt Romney’ type query generates this tagCloud […]
Combining multiple rest queries in a single Worksheet In previous posts, I covered how to get tweetsentiments into excel and how to get Google Finance data into Excel. Today we’ll look at how to build an application […]
Today’s post is about how to create a palette to use for a heatmap, and how to apply it to either an Excel Table of values to be compared, or to create surface chart and […]
Sorting chapter/bullets numbers Quite often you need to sort data that has some kind of chapter numbering, such as 1.1 1.1.2 2.1.1 2.12.2 A specialized form of this would be ipnumbers, for example 192.1.3.2 172.12.180.1 […]
Excel-jSon-Excel in one line of code On the ramblings site, I set myself the challenge to write some classes that could be invoked to convert an entire Worksheet to jSon, or alternatively, to consume a […]
Excel and Google Maps. Following my previous posts comparing the geoCoding capabilities of Yahoo, Bing and Google Maps I have now implemented Adding markers to google Maps straight from an Excel Table. As usual this is […]
Excel Data Manipulation Classes I notice from Google Analytics the the cDataSet pages are by far the most popular on the site. These classes are used throughout all the projects on the site, and are […]
Sometimes you have a range of dates and you need to come up with a scale that makes sense for the dates you are trying to represent in a timeline. For example if your dates […]
I came across a problem today, where I wanted to create a Google Intensity Map based on a selection of people in my Outlook Global Address List. I thought it would be kind of easy, […]
Using google code to host projects I moved the gadget and associated code to googlecode.com hoping to host it there. If you have never done this, it is somewhat complex. It can be found here. […]
Roadmapper update I finally got round to implementing the roadmapper as a google visualization gadget. All the source code and gadget package is available here along with the write up, so I wont repeat it […]
Google Gadgets This is the standard way to do charts in Google Docs, as well as being easily embedabble in web pages. In a previous post I started thinking about the migration path to Google Docs […]
I’ve been looking at how to use cookies to pass data between excel sessions and of course outside Excel. Having implemented a method of hiding data inside cells and Excel shapes, I figured this would […]
Roadmap Project Recap Some time ago I started to play around with a method to make Roadmaps from Excel data. Since working through the development of it on my Excel Ramblings site and in this article […]
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