Skip to content

Instantly share code, notes, and snippets.

@sancarn
sancarn / How to use a function pointer in VBA.md
Created December 30, 2020 12:14
How to use a function pointer in VBA by Akihito Yamashiro

VB6 and VBA come with no support for function pointers.

Also, when you wish to execute a function in a dll using the Declare function, you can only call functions created by the Steadcall calling conversation.

These constraints can be avoided by using the DispCallFunc API. The DispCallFunc is widely used in VB6 when erasing the history of IE. Although the DispCallFunc is known as API for calling the IUnknown interface, in fact, you can also perform other functions other than COM by passing the NULL to the first argument.

As explained in the http://msdn.microsoft.com/en-us/library/ms221473(v=vs.85).aspx , the DispCallFunc argument is as follows.

@sancarn
sancarn / CoordinateConversion.m
Created March 1, 2024 21:18
PowerQuery M language for converting from Eastings Northings to Latitude Longitude (and back)
let
//Computed Helmert transformed X coordinate.
//@param {number} X - Cartesian X coord in meters
//@param {number} Y - Cartesian Y coord in meters
//@param {number} Z - Cartesian Z coord in meters
//@param {number} DX - Cartesian X translation in meters
//@param {number} Y_Rot - Y rotation in seconds of arc
//@param {number} Z_Rot - Z rotation in seconds of arc
//@param {number} s - Scale in ppm
//@returns {number} Helmert transformed X coordinate
@sancarn
sancarn / .README.md
Created September 24, 2023 10:00
A minimal sample of optimised CopyMemory for VBA

MCopyMemory

Based almost entirely on the work of cristianbuse's MemoryModule, this is merely a minimal example and substitute for CopyMemory in VBA without any other helper functions.

Kudos to Cristian for building the original solution.

@sancarn
sancarn / CShapeEvents.cls
Last active August 9, 2023 07:58
Shape events in VBA
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "stdShapeEvents"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
@sancarn
sancarn / UpdatingSharepointRESTExample.js
Created November 29, 2022 20:25
Simple script to update sharepoint using the REST API
//Note: When porting `Authorization: "Bearer " + accessToken` will also be required in all headers!
/*
* Get all fields of a list
* @param {string} site - Url of site
* @param {string} list - Title of list
* @example await getListFields("https://myServer.sharepoint.com/sites/my/sharepoint/site", "My list name")
* @docs https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest
*/
async function getListFields(site, list){
'Example CSV:
' Column1,Column2,Result
' 1,2,
' 2,3,
' 3,5,
'Results in:
' Column1,Column2,Result
' 1,2,2
' 2,3,6
' 3,5,15

This gisst was made in response to a reddit thread here. OP Claims that ChatGPT is good at writing basic VBA code if you give it clear instructions.

Explaining code in such a way that it codes the correct thing is pretty awful though, slow and tedious... Let's try to do that for stdTimer.

The prompt I used was as follows:

I want to write a predeclared VBA class. Next I want to add public event Tick with no parameters. Next I need a remote workbook property with events and a callback object. I want a Create method which acts as a constructor, which creates and initialises an object via the protInit method. The Create method should have params milliseconds and an optional callback of type stdICallable. In the initialisation method I need to set access to the VB object model to true, then create a new instance of Application and wi

InfoNet and InfoWorks REST Servers.md

Motivation for a REST Server

Data accessibility

InfoNet and InfoWorks is renound for innaccessibility of data. Business critical asset data is often captured in models, and then never shared with the outside world. Typically the only way to share data with the outside world is via manual exports to a GIS format, and interpreting the data through other system.

This needs to change and a REST API for InfoNet and InfoWorks is an ideal candidate for connectivity of this data.

Moving to the cloud

Formula tracer

Have you ever been told to audit or understand a large formula application? Have you ever took on the challenge, and after a few hours of searching you still have no idea how the outputs relate to the inputs?

Recently I had this same issue while trying to debug issues in a spreadsheet owned by a non-profit organisation. This spreadsheet had huge tables, each table column containing formulas with one of the 7k+ relationships used in the spreadsheet. To be honest, I'm astonished Excel is capable of dealing with this number of relationships in memory with little drop in performance...

This tool can be used to create a list of every relationship in a spreadsheet, which can ultimately be boiled down into a graph showing how each sheet interacts with eachother.

![relationshis