Recursion

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

Recursion

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

Recursion

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

Recursion

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

Microsoft Office & VBA

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

Apps Script & Java Script

Performance

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

Integrating Excel with Maps and Earth

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

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

Apps Script & Java Script

Using Google Earth

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

Apps Script & Java Script

Using Google Visualization DataViews

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

Apps Script & Java Script

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

No Picture
Integrating Excel with Maps and Earth

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

Integrating Excel with Maps and Earth

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

No Picture
Integrating Excel with Maps and Earth

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

Integrating Excel with Maps and Earth

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

No Picture
Integrating Excel with Maps and Earth

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

Apps Script & Java Script

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

Integrating Excel with Maps and Earth

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

Google API

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

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

Recursion

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

Microsoft Office & VBA

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

No Picture
Snippets

Excel Comment shapes

What can you learn here? Interrupt comments Change shape characteristics Add timestamp Modifying the behavior of Cell Comment Processing Download now This article shows some techniques to interrupt the processing of cell comments in Excel […]

Integrating Excel with Maps and Earth

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

Integrating Excel with Maps and Earth

Radians Versus Degrees

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

Apps Script & Java Script

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

Integrating Excel with Maps and Earth

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

No Picture
Apps Script & Java Script

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

adsense

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

Integrating Excel with Maps and Earth

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

Integrating Excel with Maps and Earth

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

adsense

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

Integrating Excel with Maps and Earth

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

Integrating Excel with Maps and Earth

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

No Picture
Integrating Excel with Maps and Earth

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

Google API

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

Google API

Quicklinks and Categories

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

Recursion

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

Apps Script & Java Script

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

Microsoft Office & VBA

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

Microsoft Office & VBA

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

No Picture
From VBA to Google Apps Script

Finished Roadmapper Script

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

No Picture
From VBA to Google Apps Script

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

Visualization

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

Integrating Excel with Maps and Earth

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

Google API

Adding markers to Google Maps from Excel

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

Microsoft Office & VBA

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

Microsoft Office & VBA

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

Integrating Excel with Maps and Earth

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

No Picture
Recursion

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

No Picture
Microsoft Office & VBA

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

Recursion

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

Json

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

Google API

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

Microsoft Office & VBA

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

Json

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

Json

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

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

Json

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

No Picture
Json

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

Json

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

Json

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

Microsoft Office & VBA

Adhoc Rest Queries

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

Microsoft Office & VBA

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

Json

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

No Picture
Json

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

From VBA to Google Apps Script

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

Json

Scheduling updates with task scheduler

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

Json

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

Json

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

Json

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

From VBA to Google Apps Script

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

From VBA to Google Apps Script

Navigation

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

Json

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

Json

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

No Picture
Integrating Excel with Maps and Earth

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

Microsoft Office & VBA

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

Microsoft Office & VBA

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

Apps Script & Java Script

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

Recursion

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

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

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

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

Google API

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

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

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

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

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

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

No Picture
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

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

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

Cookies and Excel

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

Excel and SQL

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

arrays

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

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

Json

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

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

adsense

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

Apps Script & Java Script

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

adsense

Using Google Maps

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

Excel and SQL

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

Google API

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

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

arrays

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

arrays

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

arrays

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

arrays

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

Microsoft Office & VBA

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

Microsoft Office & VBA

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

No Picture
arrays

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

No Picture
Json

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

No Picture
Add-ons

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

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

No Picture
Json

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

No Picture
Json

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

Json

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

Json

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

No Picture
Microsoft Office & VBA

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

Apps Script & Java Script

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

Microsoft Office & VBA

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

No Picture
Json

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

Microsoft Office & VBA

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

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

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

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

No Picture
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
Visualization

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

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

No Picture
Json

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