Skip to content

Instantly share code, notes, and snippets.

@sancarn
sancarn / .README.md
Created September 24, 2023 10:00
A minimal sample of optimised CopyMemory for VBA
View .README.md

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.

View .Readme.md

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

View .DataLoadErrors.md

The Excel Error

It is quite common at least in our business that when people are building spreadsheets they accidentally link data without properly acknowledging the linkage. This can cause error messages later on which can irritate users. The error messages you might recieve often contian the text:

Microsoft Excel cannot access the file 'https://.../myfile.xlsx'. There are several possible reasons:

or

Cannot download the information you requested

View .summary.md

Now let's actually look at the code originally investigated in the author's original video ported to VBA

public sub run()
  while true
    Dim path as string
    while (path = requestedUrls.poll()) != null    
      downloads.add(new DownloadState(path))
    wend
    if not me.connectionDisabled then

Dim iterator as ListIterator

@sancarn
sancarn / UpdatingSharepointRESTExample.js
Created November 29, 2022 20:25
Simple script to update sharepoint using the REST API
View UpdatingSharepointRESTExample.js
//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){
View VBA Table update with Collection of Dictionaries.bas
'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
@sancarn
sancarn / VBA-OfficeJS bridge test.EXCEL.yaml
Last active January 3, 2022 23:36
A test to attempt to listen to VBA changes to the customXMLParts structure.
View VBA-OfficeJS bridge test.EXCEL.yaml
name: VBA-OfficeJS bridge test
description: A test to attempt to listen to VBA changes to the customXMLParts structure.
host: EXCEL
api_set: {}
script:
content: |+
/* VBA USAGE EXAMPLE
'Example from VBA:
'Requires JsBridge from here: https://github.com/sancarn/VbaJsBridge
'ScriptLab test can be downloaded here: https://gist.github.com/sancarn/b974b650f4b451ff2de51861af1671b1
@sancarn
sancarn / Blank snippet.EXCEL.yaml
Created January 3, 2022 19:43
Create a new snippet from a blank template.
View Blank snippet.EXCEL.yaml
name: Blank snippet
description: Create a new snippet from a blank template.
host: EXCEL
api_set: {}
script:
content: |
type IXMLWatcherListener = (e: any) => void
class XMLWatcher {
private interval: number;
private listeners: IXMLWatcherListener[];
@sancarn
sancarn / Deref.bas
Last active December 20, 2021 15:00
VBA Deref/Dereference objects (Convert from ObjPtr to Object)
View Deref.bas
#If VBA7 Then
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
Private Declare PtrSafe Sub ZeroMemory Lib "kernel32" Alias "RtlZeroMemory" (Destination As Any, ByVal Length As Long)
#Else
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
Private Declare Sub ZeroMemory Lib "kernel32" Alias "RtlZeroMemory" (Destination As Any, ByVal Length As Long)
#End If
Public Sub test()
Debug.Print Deref(ObjPtr(Application)).Name
View SAP_ECC.cls
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "SAPECC"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'{F9} - Jump to first form value