### Implementing a countdown timer for the Sudoku project

Why implement a countdown timer When performing a task that will take some time it is useful to show how long it has taken, and how long to go. In this section we will implement […]

### Soak Test

Testing random problems One big problem with testing these kind of solutions is that by nature, the problem is random and therefore will be different each time you test it. Unit testing can help – […]

### First Generated Puzzle

We had managed to generate a completed puzzle from scratch in the previous section, following the first step of creating a viable solver. The next step is to take the completed puzzle and remove cells from it till […]

### Generation

Planning Solving according to strategies is pretty straightforward. Generating puzzles is more complex and exercises your comfort with recursion. In general terms generating a puzzle follows these steps Create an empty grid Use brute force […]

### Assigning Parents and Children

Recap In the introduction to the Roadmapping project we created a rudimentary procedure and class that read in our data and created the basic structure we will need to produce generalized road-maps from simple tabular data. The next step […]

### SumProduct – prioritizing lookups

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 […]

### Performance

All about performance analysis, tips and tricks for getting the best out of both Apps Script and Excel. […]

### Zooming and Bounds

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 […]

### infoBox for google Map popups

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 […]

javaScript: Flying around and asynchronicity in google Earth This relates to Data Driven Mapping applications and the Javascript HowTo section Google Earth Api You will find tons of content on Google Earth , most of which is described […]

javaScript: creating google visualization Data Views This relates to Data Driven Mapping applications and the Javascript ‘HowTo’ section Google Visualization dataViews As mentioned in Using Google Visualization DataTables a master google DataTable is created for all data in […]

### Creating VizMap framework without Excel

VizMap: the framework javascript 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 Parameter WorkSheet is […]

### Vba to generate VizMap Application

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 […]

### Setting up VizMap VBA module

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 […]

### Vba Walkthrough – generating a vizMap application

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 html body

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. […]

### Using css styles to tailor InfoWindows

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 Spot Settings

This relates to Data Driven Mapping applications […]

### VizMap Application Filename

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 […]

### GeoCoding Master Data

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 […]

### Encoding client keys for Maps

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 […]

### Using Google Earth – creating KML files

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 […]

### First Shapes

Recap We’ve read our data, and set up the parameters. The data structures are working according to our debug reports, and we have dipped our toe into recursion. Now to pull it all together and […]

### Using match to categorize

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 […]

### Calculate lat/lon from distance

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 […]

### Click events in Google Earth

javaScript: Click events in Google Earth This relates to Data Driven Mapping applications and the Javascript HowTo section Events in Google Earth I deal with event handling in Google earth in Using Google Earth but dealing […]

### VizMap Controlling Execution

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 […]

### Creating a tabbed Google Mapping InfoWindow

javaScript: creating tabbed content This relates to Data Driven Mapping applications and the javaScript ‘howTo’ section. For this example I had created a  Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and […]

### VizMap Element positioning

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 Tabs

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 Measures

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 […]

### VizMap Dictionary

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 […]

### Joining transactions to master data

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 […]

### Vba to join master and transactional data

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 Output

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 […]

### Adding flight paths to Maps

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 […]

### Sorting Collections

Sorting collections is something you might need to do. In this example, we’ll use the cMyClass object we created in Get Started Snippets to demonstrate sorting a collection of objects and how to use recursion to sort the object’s […]

### End to end analysis, Excel and explorer

This is a blog post of which the excerpt is below. Click on the title to read the full post.  Read the post Visualization of Sites to Tags with d3.js

### Respect a Filter with Sumproduct

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 […]

### Sumproduct Strangeness-2

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 […]

I pretty much started here. Since the objective was to migrate from a VBA project to a Google Apps script version of the same, my approach was to write the skeleton, and emulate the underlying […]

### What to use in place of Excel Shapes

Note that this article uses the Apps Script UI service, which has since been deprecated. I leave it here for interest. The equivalent of inserting a shape in Google Docs is to insert a drawing, […]

### Adding markers to Ovi Maps from Excel

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 […]

### Adding markers to Bing Maps from Excel

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 […]

### Adding markers to Yahoo Maps from Excel

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 Yahoo 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 […]

### Complete Excel Address Data with Bing Maps API

GeoCode Excel Data with Bing Maps If you haven’t read about Bing rules on doing this, please do. Since we have done this in Google Maps, I thought it would be worthwhile to see how it’s done […]

### Serializing Excel data for Google motion charts

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 […]

### Regex, scraping and visualizing

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

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 […]

### GeoCoding

Filling in the gaps with  Mapping APIs This article will cover 2 main topics How to use the most common APIs for GeoCoding (clean up an address and find it’s co-ordinates) Comparing various GeoCoding APIS […]

### How to traverse a treeview

Treeview traversal and recursion In Rest Results Explorer i showed how to Create a treeview from json using the treeView control. Objects like the treeview are generally dealt with recursively – see Getting started with recursion and Getting to Grips with recursion for […]

### Get Data from Google Books

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 […]

### Persistence of data for userforms

In Hiding data in Excel Objects I introduced the concept of hiding data in shapes by encoding it into jSon, then hiding the serialized result in a property of a hidden shape. One of the […]

### XML to JSON with VBA

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.  […]

### Web Services

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 […]

### Using cRest directly

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 […]

### Populating sheets with Rest responses

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 […]

### Making excel tables of the results

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 arrays of objects

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 […]

### ScraperWiki mashup

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 […]

### Interesting scraperwiki scrapes

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 […]

### Getting data out of scraperWiki

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 […]

### Combining multiple rest queries

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? 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 […]

### Adding to the rest library

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 […]

### Rest Results Explorer

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 […]

### JSON stringify VBA classes

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 […]

### How to populate Excel from jSon

As covered in jSon there are many ways to get jSon data into excel. You may be tempted to try to use Excel JSON conversion or get data from google docs. These techniques are for very specific use cases – […]

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, […]

### Populating the worksheets from bitcoin api

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 […]

### Creating the dashboard

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 […]

### Creating a workbook from JSON manifest

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 […]

### Storing objects as values

Storing other objects in cJobject If cJobject was just about getting stuff in and out of JSON, then we’d stop here, but you’ll find that I use it in pretty much all the VBA examples […]

Iteration As per the examples in cJobject deep dive the most common navigation of the cJobject is by iteration through the children, or by direct access as covered in How to access children. However there are a few […]

### How to access children

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 […]

### Access shortcuts

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 […]

### Complete Excel Address Data with Yahoo Placemaker Maps API

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 […]

### Delegating xml to json conversion to GAS

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 […]

### Urbarama mashup

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 […]

### Deduce column headings from rest Query

In Rest to Excel library there are plenty of examples of populating sheets from rest queries. Usually you simply name the columns to match the data you want to extract and you are good to go. However, you […]

### Hiding data in Excel Objects

Why would you want to? If you want to learn about the cJobject and jSon with VBA you may want to start with  How to use cJobject First off I talk about ‘hiding data’. What […]

### Visualizing tables

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 […]

### Code for color scheme explorer

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 […]

### Gas hosted scripts

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 […]

### Data from parse.com

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 circles and heatmaps

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 […]

### Excel dress up doll

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 ramp layout

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 […]

### Selection of pantone ramps

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 […]

### Creating one color ramp from another

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 […]

### How to create a heatmap doughnut chart

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 […]

### Alternative schemes

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 […]

### Ramping color swatches

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 […]

### Regular Expressions

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 […]

### VBA promise implementation

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 […]

### SetTimer and VBA

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 […]

### Exponential backoff with VBA promises

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, […]

### Complex aynchronous web services

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 […]

### How to make a table from a cdataset

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 […]

### Clear data cells with cDataSet

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 […]

Using cookies straight from Excel Get it now Why would you want to do this? You know what cookies are, and think of them as being a mechanism for web sites to store information about […]

### Basic Solver

Strategies The first step is to put together a basic solver. In Sudoku, there are 3 Regions – Row, Column and Box, but you probably know that already if you are reading this. Other terminology […]

### The birthday problem

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 […]

### Tables and listobjects

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 […]

### Simple implementation of R- melt

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 […]

### Charts and color ramps

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 […]

### VizMap javaScript

VizMap: the generated javaScript 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 Parameter WorkSheet is […]

### javaScript ‘howTo’

VizMap: Javascript howTos This relates to Data Driven Mapping applications Firstly – I am not an expert in javaScript, but in the accelerated learning process to discover what was necessary to create this application generator, there […]

javaScript: Flying around and asynchronicity in google Earth This relates to Data Driven Mapping applications and the Javascript HowTo section Google Maps Api You will find tons of content on Google Maps, most of which is described […]

### Execute SQL from Excel

What can you learn here? Library of ADODB connections Populate from databases Use SQL on closed workbooks   A library to populate Excel tables with SQL query results get it now Using SQL to populate Excel […]

### Data Driven Mapping applications

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 […]

### Copying and filtering worksheets and Excel tables

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 […]

### Leveraging sorted data with Match

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 […]

### Optimizing the array formula

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 […]

### Array formulas and large ranges

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 […]

### Bit Twiddling

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 […]

### Named Ranges with Column Headings

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 Named Ranges

Organizing ranges for reuse – minimizing maintenance of data ranges […]

### SumProduct

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 […]

### Array formulas and sumproduct

Using array formulas rather than sumproduct We looked at the use of sumproduct() previously. Lets repeat that using array formulas instead. Array formulas will give you more solution options than sumproduct() can – anyway you will see that sumproduct() is just a special case of an […]

### cJobject deep dive

You’ll probably now be familiar with cJobject and want to do some fancier things. Here are some examples. You can download a vanilla cJobject workbook from the data access section of the downloads page, or construct your own with […]

### Debugging Office JavaScript API add-ins

If you already write Apps Script add-ons, you’ll know that debugging can be a little challenging, and although you can use the developer tools in chrome on a running add-on, breakpoints don’t persist between sessions […]

### Create a heatmap in Excel

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 […]

### Color Fiesta

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 […]

### Data driven VBA apps with JSON

Separating coding from defining  I think of 3 different roles in workbook app definition Consume – Use the apps and consume or maintain the data in it, and provide process, informational and operational needs Define […]

### Rest to Excel library

What can you learn here? Library of Rest Calls Populate excel tables Add your own A library to populate Excel tables with rest query results get it now I found myself writing similar code to deal […]

### Excel to json and back

JSON and Excel using VBA classes that emulate JavaScript objects, parsing and stringifying JSON and dealing with data from external services. Includes a data driven REST-EXCEL library, and implementation of Google oAuth2 authentication […]

### Excel JSON conversion

Freeing Excel Data through JSON There have been a number of topics on this site dealing with the serialization of JSON, for example to create input to google visualizations,  Hiding data in Excel Objects , Integrating Excel with […]

### jSon and Dynamic Forms

Now we know how about Hiding data in Excel Objects, Excel JSON conversion and Dynamic Forms let’s combine all that as an enhancement to the Roadmap Generation project. The objective is to embed traceability data in the generated roadmap shapes so […]

### Implementation example of form at mouse position

Implement a popup form at the mouse position  get it now Now that we know  How to position a userform on the mouse, here’s a simple example of an implementation that uses such a form to […]

### How to use the Excel Roadmapper

What can you learn here? Create roadmaps Presentation quality Learn roadmap options User Guide to Roadmapper – Get it now Elsewhere on this site, I take you through the development of a roadmapper in Excel. Looking at […]

### How to use cJobject

What can you learn here? Get started with cJobject Short code snippets Abbreviated explanations Quick examples (cDataset.xlsm) This is intended to be a fasttrack to get started with the cJobject that are used throughout the examples on […]

### How to create a tag cloud

What can you learn here? Make a tag cloud cJobject as ‘on demand’ the cTagCloud class Making a tag Cloud in Excel – Get it now Although most of the discussion about How to use cJobject is […]

### Color ramp library

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 […]

### Playing around with colors in VBA

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 […]

### Looking up color table

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 […]

### Find nearest color match

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 […]

### Color Schemes and palettes

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 […]

### Geocoding from Excel: Yahoo -vs- Bing -vs- Google

I have already looked at geocoding with Bing Maps -vs- Google Maps and Google Maps is a clear winner. Yahoo Placemaker is pretty good at first glance even though it claims not to be a […]

### Google Maps versus Bing Maps

In a previous post I covered how you could use the Google Maps Api to complete and parse addresses for subsequent mapping directly from Excel. In the name of fairness, I implemented the same thing […]

### How to use cDataSet

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 […]

### Dynamic forms and jSon

Following previous developments on hiding data in excel objects, Excel jSon conversion and dynamic forms I figured it was time to combine all these threads, and have implemented all that as an integrated solution as […]

### Getting data from Google Docs to Excel

In various posts I have covered ways to get data in and out of Excel and other PC based systems. The latest capability I have looked at is being able to get data live into […]