search

VBA-tools / VBA-Web

VBA-Web: Connect VBA, Excel, Access, and Office for Windows and Mac to web services and the web
http://vba-tools.github.io/VBA-Web/
MIT License
2.01k stars 494 forks source link

Remove Excel dependency with Application.OnTime #88

Closed joyfullservice closed 9 years ago

joyfullservice commented 9 years ago

Hi Tim, Outstanding job on VBA-Web! I love the simplicity as I use it to connect to web services on our internal software systems.

One suggestion is to remove the Application.OnTime dependency on Microsoft Excel. This works great in Excel, but not in other VBA-enabled applications like Microsoft Access.

As an alternative, I set up an additional class to utilize the Windows API for timer execution. I took advantage of a class (found here http://www.vbforums.com/showthread.php?527281-VB6-SelfTimer-class-module-2008-06-15) that even works without a wrapper in a standard module.

Using the timer class is a piece of cake. Just declare it withevents in WebAsyncWrapper and use the event to trigger the timeout functions.

Private WithEvents pTimer As SelfTimer  ' API timer class

Private Sub pTimer_Timer(ByVal Seconds As Currency)
    Call TimedOut
End Sub

This allows you to completely avoid the complexity of handling the returned timers in the standard module, and trying to sort the callbacks out to their calling instances.

Keep up the great work! I am really excited about this project, and will be using it extensively in the months ahead.

timhall commented 9 years ago

@joyfullservice Glad you found the project and thanks for bringing this up. I naively thought switching to Application.OnTime would help Access usage, but apparently it had the opposite effect! The SelfTimer looks like a good approach, I'll investigate the best way to integrate it. (Goal is to move timer code to WebAsyncWrapper so that Windows-only code like this shouldn't be a problem)

timhall commented 9 years ago

Ok, so I've moved the Application.OnTime code out from the "core" WebHelpers and into WebAsyncWrapper so that should resolve any compiling issues outside of Excel for the "core" modules/classes. I'll look into SelfTimer and other OnTime alternatives, but for now I've added a disclaimer to WebAsyncWrapper that it is Excel-only (in addition to Windows-only).