CoderFunda
  • Home
  • About us
    • Contact Us
    • Disclaimer
    • Privacy Policy
    • About us
  • Home
  • Php
  • HTML
  • CSS
  • JavaScript
    • JavaScript
    • Jquery
    • JqueryUI
    • Stock
  • SQL
  • Vue.Js
  • Python
  • Wordpress
  • C++
    • C++
    • C
  • Laravel
    • Laravel
      • Overview
      • Namespaces
      • Middleware
      • Routing
      • Configuration
      • Application Structure
      • Installation
    • Overview
  • DBMS
    • DBMS
      • PL/SQL
      • SQLite
      • MongoDB
      • Cassandra
      • MySQL
      • Oracle
      • CouchDB
      • Neo4j
      • DB2
      • Quiz
    • Overview
  • Entertainment
    • TV Series Update
    • Movie Review
    • Movie Review
  • More
    • Vue. Js
    • Php Question
    • Php Interview Question
    • Laravel Interview Question
    • SQL Interview Question
    • IAS Interview Question
    • PCS Interview Question
    • Technology
    • Other

17 September, 2023

Optional Dependency in VBA Module

 Programing Coderfunda     September 17, 2023     No comments   

Background I am developing a VBA module (call it "Greg") for VBA developers of Excel UDFs. The purpose of Greg is to enhance the experience for the end user, who uses those UDFs in Excel. The developer must simply copy a snippet into their own module (call it "Dev"). If Greg is loaded, the snippet enhances Dev's behavior. Otherwise, it has no impact on behavior, but it does prompt the Excel user to import Greg.bas. Approach Here is a simplified sketch of Greg.bas... Attribute VB_Name = "Greg" ' Keep these functions invisible to Excel users. Option Private Module ' Enhancement function. Public Function Enhancer(Optional x) ' ... End Function ' Assert the 'Greg' module is loaded. Public Function IsLoaded() As Boolean IsLoaded = True End Function ...and of Dev.bas: Attribute VB_Name = "Dev" ' Some UDF by the dev. Public Function DevRegular() ' ... End Function ' ############# ' ## Snippet ## ' ############# ' Use the enhancer, if available via the 'Greg' module. Public Function DevEnhanced(Optional x) If Enhance_IsSupported() Then DevEnhanced = Greg.Enhancer(x) Else DevEnhanced = DevRegular() ' Prompt the user to import the enhancer. MsgBox "Import the 'Greg' module to enhance your experience." End If End Function ' Check if enhancement is supported via the 'Greg' module. Private Function Enhance_IsSupported() As Boolean On Error GoTo Fail Enhance_IsSupported = Greg.IsLoaded() Exit Function Fail: Enhance_IsSupported = False End Function Problem While this has worked sporadically on Windows, it has often failed there and always on Mac. The crux seems to be Enhance_IsSupported() within Dev: Private Function Enhance_IsSupported() As Boolean On Error GoTo Fail Enhance_IsSupported = Greg.IsLoaded() Exit Function Fail: Enhance_IsSupported = False End Function I assumed that the line Greg.IsLoaded() would compile, even if there were no Greg module present...and it does! It only fails when a Greg module exists without a member called IsLoaded(). Unfortunately, it seems that VBA does not reliably refresh its "awareness" of a Greg module with an IsLoaded() function. When I import Greg.bas and Dev.bas together, everything works as intended: Enhance_IsSupported() returns True, and it updates to False if Greg is removed. But when I first import Dev.bas and run DevEnhanced(), and only afterwards import Greg.bas, then Enhance_IsSupported() apparently returns False despite the presence of Greg. Note This latter workflow is absolutely essential: an Excel user must first run DevEnhanced() and see the prompt, in order to know about Greg.bas in the first place! Failed Solutions Unfortunately, no experimentation has availed. In Greg itself, I have tried using a constant... Public Const IS_LOADED As Boolean = True ...and also a procedure: Public Function IsLoaded() As Boolean IsLoaded = True End Function In the snippet for Dev, I have implicitly tried Application.Evaluate()... Private Function Enhance_IsSupported() As Boolean On Error GoTo Fail Enhance_IsSupported = [Greg.IsLoaded()] ' ^^^^^^^^^^^^^^^^^ ' Application.Evaluate("Greg.IsLoaded()") Exit Function Fail: Enhance_IsSupported = False End Function ...but while this works for Enhance_IsSupported() itself, the same error simply crops up elsewhere — like whack-a-mole at other instances of Greg.* — and only a manual edit will "refresh" those procedures. I would also prefer to avoid unstable calls to any .Evaluate() method, even in the Worksheet scope. Questions * What is the simplest way to "refresh" the Dev module, such that its procedures now recognize the calls to Greg.*? * Is this possible without resetting cached or Static variables in Dev? * Can this be done with an IS_LOADED constant rather than an IsLoaded() procedure? Bonus * Can this refreshment be done by some Sub like Dev.Refresh(), called automatically at the start of Enhance_IsSupported()? * Can this refreshment be done by some Sub like Greg.Refresh(), as run manually by the user in the VBA editor? --- This project represents a great investment of my time and energy, and it is otherwise operational, so your help is greatly appreciated in conquering this final obstacle!
  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook

Related Posts:

  • How To Backup Your Site With Filezilla And The FTP Server You may have heard it many times that you should backup your website and the database on your computer. You can backup your … Read More
  • WordPress Hacks: Learn WordPress from Scratch 150 New idea I remember when I started learning WordPress many years ago. I didn’t know any WordPress hacks to start a brand new website.Whether you have… Read More
  • 150 Google Ranking Factors (Why Your Website is Dying) You may already know, there are more than 150 Google ranking factors which decide the SEO score of your website.The algorithm keeps changing, an… Read More
  • Adding Custom Fonts In WordPress Using @Font-Face CSS Rule For every WordPress website, to give a better look, most of the web designers add custom fonts. There are many ways, but this tutorial is about … Read More
  • How To Copy A Live WordPress Site To Localhost ManuallyThere are many people who have a copy of their website on their localhost to test the plugins and themes.It’s another step towards the WordPress secur… Read More
Newer Post Older Post Home

0 comments:

Post a Comment

Thanks

Meta

Popular Posts

  • Write API Integrations in Laravel and PHP Projects with Saloon
    Write API Integrations in Laravel and PHP Projects with Saloon Saloon  is a Laravel/PHP package that allows you to write your API integratio...
  • Features CodeIgniter
    Features CodeIgniter There is a great demand for the CodeIgniter framework in PHP developers because of its features and multiple advan...
  • Laravel Breeze with PrimeVue v4
    This is an follow up to my previous post about a "starter kit" I created with Laravel and PrimeVue components. The project has b...
  • Credit card validation in laravel
      Validation rules for credit card using laravel-validation-rules/credit-card package in laravel Install package laravel-validation-rules/cr...
  • Fast Excel Package for Laravel
      Fast Excel is a Laravel package for importing and exporting spreadsheets. It provides an elegant wrapper around Spout —a PHP package to ...

Categories

  • Ajax (26)
  • Bootstrap (30)
  • DBMS (42)
  • HTML (12)
  • HTML5 (45)
  • JavaScript (10)
  • Jquery (34)
  • Jquery UI (2)
  • JqueryUI (32)
  • Laravel (1017)
  • Laravel Tutorials (23)
  • Laravel-Question (6)
  • Magento (9)
  • Magento 2 (95)
  • MariaDB (1)
  • MySql Tutorial (2)
  • PHP-Interview-Questions (3)
  • Php Question (13)
  • Python (36)
  • RDBMS (13)
  • SQL Tutorial (79)
  • Vue.js Tutorial (68)
  • Wordpress (150)
  • Wordpress Theme (3)
  • codeigniter (108)
  • oops (4)
  • php (853)

Social Media Links

  • Follow on Twitter
  • Like on Facebook
  • Subscribe on Youtube
  • Follow on Instagram

Pages

  • Home
  • Contact Us
  • Privacy Policy
  • About us

Blog Archive

  • September (100)
  • August (50)
  • July (56)
  • June (46)
  • May (59)
  • April (50)
  • March (60)
  • February (42)
  • January (53)
  • December (58)
  • November (61)
  • October (39)
  • September (36)
  • August (36)
  • July (34)
  • June (34)
  • May (36)
  • April (29)
  • March (82)
  • February (1)
  • January (8)
  • December (14)
  • November (41)
  • October (13)
  • September (5)
  • August (48)
  • July (9)
  • June (6)
  • May (119)
  • April (259)
  • March (122)
  • February (368)
  • January (33)
  • October (2)
  • July (11)
  • June (29)
  • May (25)
  • April (168)
  • March (93)
  • February (60)
  • January (28)
  • December (195)
  • November (24)
  • October (40)
  • September (55)
  • August (6)
  • July (48)
  • May (2)
  • January (2)
  • July (6)
  • June (6)
  • February (17)
  • January (69)
  • December (122)
  • November (56)
  • October (92)
  • September (76)
  • August (6)

  • Failed to install 'cordova-plugin-firebase': CordovaError: Uh oh - 9/21/2024
  • pyspark XPath Query Returns Lists Omitting Missing Values Instead of Including None - 9/20/2024
  • SQL REPL from within Python/Sqlalchemy/Psychopg2 - 9/20/2024
  • MySql Explain with Tobias Petry - 9/20/2024
  • How to combine information from different devices into one common abstract virtual disk? [closed] - 9/20/2024

Laravel News

  • Lightning Fast Schedule Management for Laravel - 6/20/2025
  • Reset Rate Limits Dynamically with Laravel's clear Method - 6/18/2025
  • Manipulate Image URLs in Laravel with the Image Transform Package - 6/19/2025
  • Handle Nested Arrays Elegantly with Laravel's fluent() Helper - 6/18/2025
  • Laravel 12.19 Adds a useEloquentBuilder Attribute, a FailOnException Queue Middleware, and More - 6/18/2025

Copyright © 2025 CoderFunda | Powered by Blogger
Design by Coderfunda | Blogger Theme by Coderfunda | Distributed By Coderfunda