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 […]
These are a much overlooked feature of Excel, largely because array functions can be hard to understand. The power of many regular formulas such as countif, sumif that you use every day can be increased […]
Many people have a love hate relationship with what is perhaps Excel’s most powerful capability. Normally you don’t use them enough to become completely comfortable with them, but when you do you wish you had […]
There’s a lot of stuff on this site about both JavaScript and VBA. In addition to all the JavaScript that people are generating for Apps Script, there are millions of libraries and useful pieces of […]
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. […]
Using promises in JavaScript and other languages is the preferred way to orchestrate the completion of asynchronous activities. Here’s a (not perfect but fun) Promise implementation in VBA. Example includes getting data from Google Fusion tables. […]
GeoCode addresses with Google Maps API If you haven’t read about Bing rules on doing this, please do. In this section, we will look at what Google responds to a request for an address geoMapping. Using […]
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 […]
Apps for Office (the JavaScript API for office), is a way of using JavaScript along with Office. This section is all about Microsoft APIs that you might need for working with this. Office Add-ins – first […]
UPDATE parse.com as now been closed and moved to parseplatform.org. I will update the content of this page an move the back-end to this platformis text. I like scriptDB, but in many ways I prefer […]
If you are migrating from Office to Google Apps (or indeed the other way) a useful way to learn is through a bakeoff process. This is where solutions are shown for the same problem using […]
In Watching for server side changes from the client html service I showed a method of noticing and reacting to server side data changes from a client side Apps Script add-on. It boiled down to this […]
In Google Apps Script there are 3 PropertiesServices. Document, User and Script. Office doesn’t have a direct equivalent, but we can create something for the Document and Script/User properties fairly easily. It’s not as good, […]
If you are porting an Apps Script add-on to an Office add-in, you may find you’ll be dealing with competing load methods if you are using google.load to bring in some google APIS. Office Add-ins […]
Office has the concept of an Add-in (previously these were called Apps for Office as well as the JavaScript API for office). At first sight they look pretty much the same as Apps Script Add-ons – […]
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 […]
Summary CDataSets is a collection of cDataSet You can find the methods and properties documentation on Github. And the source code is here
What is this These few pages are about how to create a Sudoku Solver and Generator using Excel/VBA. If you just want one, then go ahead and download it. If you are interested in how to […]
Creating heatmaps An easy way to create heatmaps is to use conditional formatting in your spreadsheet. However, we are going to look at creating heatmaps as an Excel Chart, and also to automatically create a […]
The mathematics of color is a complex and interesting topic. Here you’ll find Google Apps Script, VBA and javascript examples of color manipulation including finding the closest match to a given color in a defined color scheme, and the infamous heatmap doughnut chart. There are lots of references to color on this site, and it seems to be a subject second only to jSon in popularity […]
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 […]
Does Excel need optimization ? Generally speaking no. The normal use of excel with a few sheets and straightforward formulas does not need any optimization. However, sometimes workbooks grow, especially if you try to have […]
What can you do with this? Profile Workbooks Profile Code Automatic Code Profiler It’s all about optimisation This section looks at how to exercise a workbook to identify the calculation speed of every single column, and also […]
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 […]
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 […]
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 […]
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 […]
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 […]
These classes provide the tools and data structures needed to separate your physical data in your spreadsheet, from how you process it in your procedures. Notice that the row and column numbers begin at 1, […]
The cCell class addresses and manipulates a single excel cell and its contents. You can find the methods and properties documentation on Github. The code is also below ‘gistThat@mcpher.com :do not modify this line – see […]
In Using Google Maps I covered how to create various web based interactive maps. Of course many would like to use Excel to create maps, and the topic of chorpleth maps, has been covered many times by […]
Geocoding, Mapping and creating data driven applications straight out of Excel get it now This article will cover 3 main topics. I wrote all this stuff a few years ago, and some of the services may […]
What can you learn here? scriptcontrol object call from VBA use javaScript The ScriptControl Object This object gives you access to a scripting engine that will accept scripting in both javaScript and VBScript. Since this site has […]
What can you learn here? scriptcontrol object make VBArrays make javaScript arrays Array formats In How to use javaScript from VBA I covered how to use the scriptcontrol object to ‘extend’ VBA by scripting in javaScript. […]
What can you learn here? Public versus Private Variable visibility External References If you read the section on procedure arguments you will have noticed the great capabilities in VBA (and all languages) for passing data between discrete […]
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 […]
What can you learn here? Making a tag Cloud in Outlook Here is an example of how to implement a tag cloud class. Once you have implemented a tagCloud in Outlook you can ‘get the […]
What can you learn here? ramp color scales gradient between colors Use a ramp library Generalized Color Ramps If you read A tagCloud in Outlook, How to create a tag cloud or Create a heatmap in Excel you would have […]
There are a few items on this site that cover color ramps and other color manipulation topics. I’ve built up a few snippets on color management in VBA so I thought I should centralize them for easy […]
In Playing around with colors in VBA I introduced the concept of a color table. This is just a list of a name and a matching hex color code in a big excel table. I have been […]
Let’s say you have a color, and you want to find the closest match in defined color scheme set. The first problem is defining what closest means. It’s not a simple problem to solve. There […]
These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available for directly from O’Reilly or Amazon. Many applications have their own names for colors in their palettes. This example […]
These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available from O’Reilly or Amazon. As the migration progresses, there is less and less happening in VBA, and its role […]
These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available from O’Reilly or Amazon VBA does not naturally support JSON, but to be able to use this API (and […]
These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available directly from O’Reilly or Amazon. To be able to use the Execution API, various assets that are not naturally […]
I’ve been dong a lot of writing over on Desktop Liberation looking at techniques to run Apps Script/JavaScript code directly from VBA, In this post I’m going to talk about how useful this can be if […]
If you develop shared code for VBA, you’ve probably got some massive workbooks that contain multiple projects that you wish you could separate, but it’s too complicated because they are sharing classes and functions. The […]
Last year, I started to experiment with Google Universal Analytics to measure usage of Excel Apps that people download from Excel Liberation in order to get insight into what’s being used, how it’s being used and […]
If you take data from outside Excel – especially using REST APIS, there’s a good chance that you’ll have to deal with Unix Times. If you are familiar with the Excel Liberation site, you’ll know […]
Last week, I posted how to instrument Excel workbooks so that usage of them could be tracked using Google Analytics. Now we have a week’s worth of data, let’s see what happened. Instrumentation Recap First […]
In Rest-Excel library, I have examples of a large number of REST APIs which get data from a wide range of data sources. I have a similar library for Google Apps Script. I have to […]
If you are familiar with this blog and the Excel Liberation site, you’ll know that there is a pretty significant focus on getting stuff in and out of Excel. Ease of JSON and javaScript like […]
In Google Apps Script lists and validation I showed how to use custom functions from Google Spreadsheets to perform common spreadsheet tasks like lookup, index, filter, sort, match and apply dataValidation using cloud based lists. Of […]
Being able to create Sankey Diagrams in D3 directly from Excel continues to be one of the most popular topics on the Excel Liberation site even though I first posted this a long time ago now. […]
Today’s post is a quick function for creating biased randoms from a list of items and weights. You might need this to create some test data, where the outcomes are not completely random. Example […]
Yesterday I showed how to do some data wrangling in VBA and Google Apps Script that Tony Hirst on his oUseful blog had done using scraperwiki, Open refine plus various APIs. Today he posted a quick analysis using […]
I just saw a great post by Tony Hirst on his oUseful blog where he nicely laid out how to use Open refine plus various APIs to research which candidates were standing for election in […]
In documenting VBA procedures automatically I showed how to create web pages that documented your VBA projects. I’ve updated that now to also include a popup showing the detail of function arguments too. This page was automatically […]
jSon /VBA functions have always been the most popular topic on the Excel Liberation site and forum. The interesting part about this though is not so much about parsing and stringifying jSon data (which after all is […]
In playing around with color in VBA I introduced a bunch of functions to manipulate various color spaces from VBA. Digging into the whole topic a little more, I wondered if it would be possible to […]
In a previous post I covered a way of automatically including and inserting VBA code in Excel from gists. You would normally use this if you wanted to include a bunch of functions and classes […]
Here’s a little bit more on the subject of VBA string manipulation. I showed in a post the other day how things take longer and longer when concatenating large strings in VBA. One reason I […]
We had a question on our forum the other day about whether it would be possible to inherit the cell colors from the color of the originating cells in Excel in the d3.js Sankey diagram […]
I find myself doing a fair bit of VBA at the moment, It was a little adjustment coming back, but I suddenly noticed that string concatenations were taking a long time. For example, this would […]
Figuring out how to serialize native VBA classes to jSon is something I’ve been working on in the background for some time. If you’ve been following this blog or the related Excel Liberation Site, you […]
I saw a great blog post on Reshaping Horse Import/Export Data to Fit a Sankey Diagram from Tony Hirst a few minutes ago. In it he shows how to mashup various bits and pieces using […]
You may have used the doughnut chart in Excel in order to produce a chart like this This is fine, but the problem is that the position of any category changes depending on the data. […]
I came across a great site, builtwith.com, that shows the technology being used in a given web site. You can also take a look and see the usage of various specific technologies over time. Now, […]
Enterprise Architects often like to refer to Winchester Mystery House as an example of what you get if you build something with no plan, blueprint or end game in sight. This is a house with windows opening […]
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 […]
Roberto Mensa share this post today on G+, reminding me about the Google instant graph capability, and of course I wondered if the same thing could be achieved in Excel. Excel doesn’t really know how to […]
Today’s entry is pretty cool. I came across the urbarama site, which describes itself as “a collaborative world atlas of architecture and civil infrastructure”. What’s more it has an API, which will return details on […]
In scraping the scraper I showed how to get scraperwiki data into both Excel and Google Apps Script. More interestingly though, I was talking about data for which others had already done the legwork to create […]
In this post about using the scraperwiki API I covered how to get a list of publicly available scraperWiki data straight into Excel and Google Apps Script. Now you can get the associated data too with just […]
Unlike Google Apps Script, which has a fine cloud based library capability, sharing and keeping code current for non-professional developers who use VBA is challenging. The use of add-ins for finished products with specific capabilities […]
You have probably read that jSon works well with javaScript because ‘it is javaScript syntax’. Consider this javaScript (actually Google Apps Script). var items = {itema:1,itemb:’b’}; Logger.log (items.itema + “,” + items.itemb); We […]
I just added a couple of things to cDataSet.xlsm and noticed that I had done this over 100 times. During the same time period, visits to https://ramblings.mcpher.com/ where this is hosted have gone like this The top […]
I had some questions about authentication from users of the Excel Rest library. Typically Rest queries are authorized through the use of a developer key issued to registered users, but some sites need authorization as […]
In a previous post I covered how to navigate REST responses using a simple treeview control as well as how to create a Treeview from any jSon object in a few lines of code. Recursion […]
If you open an Office Document, you might occassionally find your self with an error that tells you that your “object library is invalid” , “excel has had a catastrophic failure” or you have an […]
In a post the other day i covered how to use the scriptcontrol to call javaScript directly from VBA. Considering that javaScript has plenty of useful methods for manipulating arrays and strings and so on […]
Those of you that read this blog, and the ramblings site, will know that the topic content is about equally split between VBA and javaScript. Until today, I had no idea that there was a […]
I got this idea from Tony Hirst’s great blog. The OpenCorporates API gives rich information many companies. Using Google Refine to reconcile, it cleans up many sources of data to a queryable datasource. So here […]
Lets say you have an Excel workbook consisting of tabs, all in the same format, that various people completed. You want to copy them all to a consolidated sheet from time to time so you […]
Color Ramps In a previous post, I showed how to automate the\ creation of a heatmap in Excel. It occurred to me that this could be generalized to any color ramp. Here is an extensible […]
tagCloud in Outlook In a previous example, I showed how to create a tagCloud of various Excel data sources, with a downloadable example on the ramblings web site It occurred to me it would be pretty […]
The tagCloud class Today’s post will demonstrate how to generate tagClouds from various sources in Excel. As an example we will use some tweets retrieved by the rest- Excel library A ‘Mitt Romney’ type query generates this tagCloud […]
Combining multiple rest queries in a single Worksheet In previous posts, I covered how to get tweetsentiments into excel and how to get Google Finance data into Excel. Today we’ll look at how to build an application […]
Today’s post is about how to create a palette to use for a heatmap, and how to apply it to either an Excel Table of values to be compared, or to create surface chart and […]
Sorting chapter/bullets numbers Quite often you need to sort data that has some kind of chapter numbering, such as 1.1 1.1.2 2.1.1 2.12.2 A specialized form of this would be ipnumbers, for example 192.1.3.2 172.12.180.1 […]
Excel-jSon-Excel in one line of code On the ramblings site, I set myself the challenge to write some classes that could be invoked to convert an entire Worksheet to jSon, or alternatively, to consume a […]
Excel and Google Maps. Following my previous posts comparing the geoCoding capabilities of Yahoo, Bing and Google Maps I have now implemented Adding markers to google Maps straight from an Excel Table. As usual this is […]
Excel Data Manipulation Classes I notice from Google Analytics the the cDataSet pages are by far the most popular on the site. These classes are used throughout all the projects on the site, and are […]
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 […]
Sometimes you have a range of dates and you need to come up with a scale that makes sense for the dates you are trying to represent in a timeline. For example if your dates […]
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 […]
Classes for Excel Data Abstraction In a number of articles and topics on my website i have referred to a collection of classes that I generally use to abstract Excel physical location from the data contents. […]
I came across a problem today, where I wanted to create a Google Intensity Map based on a selection of people in my Outlook Global Address List. I thought it would be kind of easy, […]
Using google code to host projects I moved the gadget and associated code to googlecode.com hoping to host it there. If you have never done this, it is somewhat complex. It can be found here. […]
Roadmapper update I finally got round to implementing the roadmapper as a google visualization gadget. All the source code and gadget package is available here along with the write up, so I wont repeat it […]
Google Gadgets This is the standard way to do charts in Google Docs, as well as being easily embedabble in web pages. In a previous post I started thinking about the migration path to Google Docs […]
I’ve been looking at how to use cookies to pass data between excel sessions and of course outside Excel. Having implemented a method of hiding data inside cells and Excel shapes, I figured this would […]
Roadmap Project Recap Some time ago I started to play around with a method to make Roadmaps from Excel data. Since working through the development of it on my Excel Ramblings site and in this article […]
Can we use cookies inside Excel This is a follow on from my thoughts around using JSON both to store data inside Excel shapes and cells, and also to pick up data from external sources. […]
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines