No Picture
Microsoft Office & VBA

Using VBA to consume a rest blister query

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

No Picture
Microsoft Office & VBA

Getting lists as a restquery

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

Google API

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

Integrating Excel with Maps and Earth

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

Integrating VBA and Javascript

Getting started with VBA

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

From VBA to Google Apps Script

classes and javaScript

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

VBA

Basic HTTP Authentication from VBA

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

VBA

Equation plots in Excel

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

Microsoft Office & VBA

Data Abstraction with cDataSet

This section has moved. Please see the links below. How to use cDataSet Data manipulation Classes in Google Apps Script cDataSet Model

Colors

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

Colors

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

Apps Script & Java Script

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

Colors

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

Colors

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

Colors

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

Colors

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

No Picture
Colors

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

Colors

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

Colors

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

Colors

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

Microsoft Office & VBA

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

Microsoft Office & VBA

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

Microsoft Office & VBA

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

Microsoft Office & VBA

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

Microsoft Office & VBA

asynch ADO using promises

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

Microsoft Office & VBA

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

Microsoft Office & VBA

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

VBA

Getting Started with VBA Classes

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

Microsoft Office & VBA

Classes: Introduction

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

Microsoft Office & VBA

cHeadingRow

  Summary This 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

[…]

Microsoft Office & VBA

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

Colors

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

Microsoft Office & VBA

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

Microsoft Office & VBA

Get Data from Outlook

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

Microsoft Office & VBA

cDataColumn

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

Microsoft Office & VBA

cDataRow

This is a row of cCell . The sid argument refers to the column either by column name or number. Where sid optional and omitted the complete row is operated on. Where sid is present the cCell it addresses in the cDataRow is operated on.  For example cDataRow.Refresh() […]

Snippets

Formatting range addresses

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

Microsoft Office & VBA

Automatic documentation

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

How to use cDataSet Model

cDataSet Model

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

Microsoft Office & VBA

CDataSets

Summary This is a collection of cDataSet You can find the methods and properties documentation on github. And the source code is here

Colors

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

Colors

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

VBA

How to use cDataSet

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

Microsoft Office & VBA

cCell Class

This 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

 

Microsoft Office & VBA

cDataSet

Summary This is a block of cCell  consisting of a collection of  cDataRow, with an abstraction of a collection of cDataColumn The sid argument refers to the column either by column name or number and the rowID refers to the particular row by number, […]

Colors

Coloring maps and other shapes

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

No Picture
Microsoft Office & VBA

How to pass arrays between javaScript and VBA

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

Colors

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

Colors

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

Colors

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

Colors

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

Colors

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

No Picture
GitHub

GitHub and VBA integration

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

No Picture
Microsoft Office & VBA

Batman plot in Excel

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

No Picture
Javascript

Using JScript inside VBA

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

No Picture
Microsoft Office & VBA

OpenCorporate data Excel REST query

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

No Picture
VBA

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