No Picture
Microsoft Office & VBA

REST access to blister functions

To take the idea covered in Getting lists as a restquery a little further, why not simple expose all the custom blister functions so they can be queried too. This means that you can use exactly the same […]

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

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

Microsoft Office & VBA

Results method

cprocProfiler.Results (rOutput as range) purpose Reports on this profiling session.   arguments rOutput – This is a range representing the first cell (upper left) where the report for this profiler is to be written to and […]

Microsoft Office & VBA

Pause Method

Public Sub Pause(Section As String) purpose Pauses a timing section that has previously been started with the Start method. The main use of the Pause method would be to exclude certain timings, for example the time spent waiting for user […]

Microsoft Office & VBA

Start method

cProcProfiler. Start(Section As String, Optional ProcName As String = “Not given”, Optional pLevel As ptLevels = ptLevelLow) purpose Starts a timing section that will last until the Finish or Pause method is executed. The first time it is called for a given section it […]

No Picture
Microsoft Office & VBA

FinishProfiler method

FinishProfiler  purpose Marks the end of a profiling session and stops all timing activity.   arguments none returns none For help and more information join our forum, follow the blog or follow me on Twitter

Microsoft Office & VBA

Finish method

cProcProfiler.Finish(Section As String) purpose Finishes a timing section that has previously been started with the Start method.    arguments Section – the name of the Code section. This is the key through which you access this in all […]

Microsoft Office & VBA

destroyTimers method

cProcProfiler.destroyTimers  purpose     Clean up when a profiling session is complete and the results have been reported    arguments  none  returns     none  Notes – Advanced     If you are only using one profiler, you will […]

No Picture
Microsoft Office & VBA

StartProfiler method

cProcProfiler.StartProfiler(sname As String, Optional pLevel As ptLevels = ptLevelAll) purpose Starts a profiling session that will last until the FinishProfiler  method is executed.  arguments sName –Purely informational and results will be reported against this name. In more […]

Microsoft Office & VBA

ptLevels enum

Public Enum ptLevels ptLevelNone     ptLevelLow ptLevelMedium ptLevelHigh ptLevelAll purpose These are given  when creating a cProcProfiler or starting a cProcTimer. They allow you to filter which sections to include in a profile session without having […]

No Picture
Microsoft Office & VBA

procProfilerWrapper module

procProfilerWrapper Normally one cProcProfiler object is enough. A couple of wrapper procedures are provided so you can use the default profiler session without the need to create your own. Public Methods Usage  Name  Returns Argument(s) […]

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

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

No Picture
Integrating Excel with Maps and Earth

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

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

No Picture
Cloud Functions

Mapping custom domains to cloud functions

This is one of a series of articles about Going serverless with Firebase. You many want to read about Firebase cloud functions and Custom domains and ssl with Firebase hosting before this article. Redirecting requests to your custom domain Now […]

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
Microsoft Office & VBA

Which VBA references you are using

VBA references Quite often you need to add some reference to be able to get some code to compile. This is because the code is referencing some external object that the Excel VBA does not […]

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

No Picture
VBA

Looping through files in a directory

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

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

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

Microsoft Office & VBA

cProcTimer

cProcTimer Class Normally you do not need to access this class. However, some of the properties can be useful for more advanced applications and rogue use case identification. Public Methods Usage  Name  Returns Argument(s)  Summary […]

Microsoft Office & VBA

cProcProfiler

cProcProfiler Class Your main interaction with the profiler is through this class. The Usage column indicates the type of profiling session in which you are likely to need to access a particular property or method. […]

Microsoft Office & VBA

Advanced profiling: Rogue Use Cases

Identifying rogue cases – (by that I mean a combination of circumstances that cause a normally well behaved procedure to take forever), is very simple using these profiling tools. For the sake of this example, […]

Microsoft Office & VBA

Objects and the garbage collector

When I first started playing around with VBA, everything I read emphasized how important it was that when you initialized an object, you remembered to set it to nothing afterwards in order to ‘release the […]

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

No Picture
Apps Script & Java Script

Get Started Snippets

What can you learn here? Get started Short code snippets Abbreviated explanations Quick examples Get it now This articles on this site are largely about how to do quite big projects. Realizing that you may […]

Microsoft Office & VBA

Use a spreadsheet as a database in 5 minutes

This tutorial will get you started with Database abstraction with google apps script and show you how to write a small app that uses a sheet as a database. https://docs.google.com/presentation/d/10rEyXjHxo42YV3wRW46o-CgrAZxFqUxyftHPkjLtbt8/embed?authuser=1&hl=en&size=l   Open do something in 5 with gas-1-use […]

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

Apps Script custom functions

Just like Excel, you can extend Google Sheets by writing custom functions. These functions are written in Google Apps Script. In this section, I”ll show how to do some common VBA things you might want […]

From VBA to Google Apps Script

Apps Script timer collection

One thing I’ve found you need when developing for Google Apps Script is a timer. Everything is pretty slow ( see Optimizing access to sheet values) so you need to dig into exactly what is good […]

No Picture
Docs

VBA debug object

There is a debugger for google apps script. Here’s how to use it Compared to the VBA debugger it sucks, so you probably need to be able to log and assert. javaScript does not have […]

No Picture
From VBA to Google Apps Script

Apps Script versus VBA scope

In Understanding Scope for VBA I showed some examples of the visibility of functions and variables across modules and procedures inside a VBA project. If you are moving to Apps Script (or JavaScript) from VBA, you’ll need […]

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

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

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

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

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

No Picture
From VBA to Google Apps Script

cJobject Class in Google Apps Script

The Rest to Excel library is a set of classes and modules that simplify the populating of Excel sheets with data from RestFul APIs. Google Apps Script of course already has plenty of much better ways to […]

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

Fusion data to Excel

In Flight data from Fusion I showed how to get a large amount of data from various Google Fusion tables into a javascript app. Since the Fusion API is just a REST API, you can use the Rest […]

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

No Picture
Microsoft Office & VBA

Advanced rest library usage

What can you learn here? Using cDataSet Using cRest Using cJobject Doing unusual things with the rest library get it now Having dealt with Rest to Excel library, Adding to the rest library, Adhoc Rest Queries and Populating sheets with Rest […]

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

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

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

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

No Picture
Apps Script & Java Script

Mapping the demo

By the time that A gigantic pub crawl gets here, then various components will have suggested pubs inside an area and declared pub crawls. We can use the Maps API to work out the optimal route between […]

Microsoft Office & VBA

Profiling user defined functions

Once you’ve analyzed your workbook for columns which are slow to calculate, it may be that the evidence is showing that some of your user defined functions need to be optimized. Or perhaps you have […]

No Picture
Microsoft Office & VBA

Prettify dynamic grids

Using textboxes instead of grids. Adding grids such as Flexgrid to VBA userforms is fraught with difficulties to do with versioning, security and so on. Sometimes you have to just bite the bullet and use […]

No Picture
Json

Dynamic Event Handlers

Creating event handlers dynamically Normally, when creating a control on a form within the development environment, you assign your code for event handling through the control. However, when your controls are created dynamically, clearly you […]

No Picture
Microsoft Office & VBA

Dynamic Forms

Creating forms dynamically Creating forms and form controls using the IDE is pretty straightforward. When the form is rather static, you can lay it all out in advance. However when the layout of the form […]

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

No Picture
Apps Script & Java Script

Let Google Apps Script do the work

For this site, I usually create a Google Apps Script version and a VBA version of the projects and libraries discussed. This is not so hard, since there are utility libraries in both languages, so it’s […]

Microsoft Office & VBA

more complex parse API topics

We’ve dealt with parse.com – nosql database for VBA, cParseCom examples and shown the code at parse.com api class for VBA. Here’s a few selected topics to help you understand a lottle more about how […]

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

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
Microsoft Office & VBA

Mapping and adding markers to maps

Filling in the gaps with  Mapping APIs This article will cover 2 main topics How to use the most common APIs for adding markers and information layers to Maps How to plot Excel data directly […]

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

No Picture
Microsoft Office & VBA

gistThat Idea

Introduction Usually when I write about something here, I’ve either finished it, or at least done a proof of concept. This is just an idea at the moment and if you would like to contribute […]

Microsoft Office & VBA

How to update modules automatically in VBA

I first started to think about this problem when I found myself having to tailor workbooks for particular capabilities described on this site. I’m not great a fan of add-ins (for libraries), and I wanted to find a way of making the code openly available, and at the same create a general capability for people to share and update VBA code painlessly, either publicly, or between their own workbooks. […]

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

Microsoft Office & VBA

parse.com – nosql database for VBA

I use scriptDB a lot , both with Google Apps Script and from other sources, including VBA, and in this blog post, I compare parse.com performance with script DB. One of the things that Parse.com […]

Microsoft Office & VBA

Strings and garbage

VBA has a garbage collector that runs from time to time in order to free up memory that is no longer being used. in Objects and the garbage collector I showed how to teardown objects so they […]

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

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

arrays

Sudoku constraints

Developing a constraint formula to identify naked sets During the development of the sudoku solver it occurred to me that there had to be a better way to look for naked sets than to try a pair, then […]

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

cHeadingRow

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

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

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

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

No Picture
Microsoft Office & VBA

cDataRow

cDataRow 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() […]

Microsoft Office & VBA

Execution Time Logging

Sometimes recalculation seems to take a long time and you cant find the formula that is slowing it all down. The advice you find is usually generic, telling you that function X() is slow etc, […]

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

No Picture
Microsoft Office & VBA

Automatic Profiling

What is this about? If you want to press a button and include all the code you need to analyze your VBA code, this is what you need, and you can be up and profiling […]

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