I’ve been doing an annual update called ‘a year in Apps Script’ for a number of years now, but I didn’t get round to the 2021 post. This year I thought I’d do something different. […]
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 […]
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 […]
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 […]
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
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 […]
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 […]
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 […]
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 […]
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) […]
In Integrating VBA and Javascript I showed how you could run your JavaScript and Apps Script source locally on your PC from VBA. In this example, we’ll use that capability for comparative unit testing. Again […]
In Integrating VBA and Javascript I showed how you could run your JavaScript and Apps Script source locally from VBA. Let’s take a look at how they perform. Recap You’ll recall that we can pull […]
In Integrating VBA and Javascript I showed how you could run your JavaScript and Apps Script source locally on your PC from VBA. The example in Local VBA versus JavaScript performance used apps script code […]
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 […]
What is the infoBox An infoBox is an enhanced infoWindow. Details can be found here . I will be converting the Google Maps modules on this site to use the infoBox rather than the native Google Maps […]
javaScript: Flying around and asynchronicity in google Earth This relates to Data Driven Mapping applications and the Javascript HowTo section Google Earth Api You will find tons of content on Google Earth , most of which is described […]
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 […]
For an overview of what VbaGit is, please read Integrate VBA with Github. If you are working with Google Apps Script as well as VBA, then you will probably be familiar with JDSOC. This is a way […]
For an overview of what VbaGit is, please read Integrate VBA with Github. Getting the code You can get the code from github or you can get a premade bootstrap workbook from the github stuff section in the downloads […]
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 […]
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 […]
Here’s a real example of using the ChromeTrace library to take a look at what a triggered task is up to, and examine what we can see in the tracing tool. I’m going to add some tracing […]
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 […]
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 […]
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 […]
javaScript: creating tabbed content This relates to Data Driven Mapping applications and the javaScript ‘howTo’ section. For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and […]
VizMap: 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 […]
Deprecated Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap […]
Tailoring the 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 […]
Overlaying polyLines to make flight paths on google markers This article will cover how to add flight paths to Google Maps. For a quickstart, see Step by Step Guides Try it out first ? If you […]
Navigating using quicklinks and categories Try it out first ? For a summary step by step explanation see How to add quickLinks to a Map If you just want a to try it, then you can […]
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 […]
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. […]
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, […]
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 […]
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 […]
Where to get the document This page concerns the From VBA to Google Apps Script project. You can find the code and test data, under the roadmap section in the VBA to Google Apps Script Roadmapper project download. You […]
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 […]
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 […]
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 […]
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 […]
JavaScript does not really have the equivalent of a VBA collection. Since the use of collections is very significant in this project, the best approach is to emulate a collection object in javaScript so that […]
Arguments to procedures in VBA can be optional, and optionally they can have a default value. For example We can also test for the presence of a variant argument by using isMissing(). See Getting Started with […]
Things to look out for As you work with javaScript (most of these gotchas will be about javaScript rather than apps script specifically), you’ll scratch your head about a few things. I know I did. […]
You can find the code (its a work in progress) and test data in the VBA to Google Apps Script Roadmapper project download To minimize the amount of work in converting from VBA to Google Apps […]
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 […]
Google apps script is javaScript, with the addition of a set of classes which allow access to Google apps capabilities such as spreadsheet data structures and so on. This means that to be able write […]
Here’s what you need. This is written from the perspective of A VBA API for scriptDB, but most of it also applies to scriptDB API for JavaScript Work your way through the material on A VBA API for […]
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 […]
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 […]
Put addresses on a map as markers We’ve already looked at how to geoCode addresses using various APIs and how to create maps with Google Maps from Excel data. Now let’s look at using Yahoo Maps to put […]
We’ve already looked at how to geoCode addresses using various APIs. Now lets look at using Google Maps to put markers on a Map using an Excel worksheet as input. As usual you can find the […]
GeoCode Excel Data with Bing Maps If you haven’t read about Bing rules on doing this, please do. Since we have done this in Google Maps, I thought it would be worthwhile to see how it’s done […]
When converting from VBA, unless you are abandoning VBA, it’s just as well to mimic some of the functions that are missing from GAS so that you can minimize your script Conversion work. Here is […]
In How to update modules automatically in VBA, you can see how to add and update modules from github directly in VBA. This post on the Excel Ramblings blog, will take you through a step by step […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives. I leave this article here for interest.In Using scriptDB, I covered some usages of scriptDB,along with a method of siloing data […]
scriptDB is a javaScript object database for Google Apps Script project. Here are a few key facts Every project can have its own scriptDB There are no Tables (this is a noSQL database). Instead it is […]
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 […]
This section has moved. Please see the links below. How to use cDataSet Data manipulation Classes in Google Apps Script cDataSet Model
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 […]
Project conversion We will use the Roadmap Generation project as a model for learning how to take an existing, complex project and convert it from VBA to Google Apps Script. This is implemented as a one liner […]
Color manipulation library scripts First off for this project we are going to need to figure out how to manipulate colors in Google Apps Script. As per the approach in Roadmapper migration my objective is to make […]
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 […]
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 […]
Deprecated Parse.com has now become parseplatform.org and i will be investigating it at a later stage. I am however leaving the page as if as you may still find it useful. Contact me if you […]
Overlaying 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 […]
Following on from Coloring maps and other shapes, here’s a bit of fun that takes the tables of Pantone colors of the year dealt with in Selection of pantone ramps, and applies them to a […]
Alternative 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 […]
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 […]
Project conversion We will use the Color ramp library project as the basis for this migration. The complete code can be found here Google Apps Script Color Ramp workbook Status of conversion General libraries You can read about these […]
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 […]
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 […]
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 […]
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 […]
Using the modules from Automatic documentation, here’s how to create a skeleton Google Apps Script Module directly from VBA modules or classes. It will also create basic JSDOC markup for documenting your new GAS module. You’ll find all […]
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 […]
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 […]
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, […]
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 […]
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 […]
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. […]
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 […]
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 […]
What can you learn here? ramp color scales using in surface charts brighten/darken Creating Charts with colorRamps Create a heatmap in Excel showed how to make charts using the heatmap color ramp. Now that we have […]
VizMap: the generated javaScript This relates to Data Driven Mapping applications For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and the example Parameter WorkSheet is […]
javaScript: 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 […]
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 […]
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, […]
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 […]
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. […]
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 […]
It would be really interesting to be able to look at how people use things that you post. Google Analytics give a good handle on how your web applications and sites are being used, but […]
Migrating from VBA to Google Apps Script, or using them simultaneously can be hard to get started with. Although this material has been largely superceded by Going Gas, there’s stll some useful stuff here. […]
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 […]
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 […]
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? 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 […]
Implementing an asynchronous timer for Excel Get it now This article will show you how to implement a progress bar or countdown timer in VBA, and give you a downloadable example and reusable classes to get you started. […]
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 VBA does not naturally support JSON, but to be able to use this API (and […]
The REST API interface is pretty straightforward, but it is JSON. VBA doesn’t natively support JSON, but this library includes cJobect , which deals with all of that for you. This packages all the things you can […]
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 […]
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 have already looked at geocoding with Bing Maps -vs- Google Maps and Google Maps is a clear winner. Yahoo Placemaker is pretty good at first glance even though it claims not to be a […]
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 […]
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 […]
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