4

I run on a daily basis a set of VBA-rich Excel files. Most of them include MS Office application cross-talk, but also employ third-party applications and MySQL. Due to the fact of running those files in a specific order and at a specific time of day I set up an xlsm-based scheduler to run those files and control outputs. Since my 'application' is growing I'm facing a problem of resource usage and typical garbage collector errors in VBA (not mentioning lack of active VBA support from MS, poor IDE and debugging etc) therefore I'm looking for other ways of automation.

Processes I'm running are typical ETL from 3rd party sources and CRMs & RDBMSs, integration of the data to edible by non-DataScience users and tools (must cover Excel 64- and 32-bit architecture). Most of it runs along data validation after imports, type testing before further analysis, sometimes picture2data replacement, applying business logic to data, pushing outlook notifications, SharePoint data I/O etc.

My question is whether PowerShell-based scheduler running those VBA and VB scripts may be more effective when it comes to memory usage or it will only as effective as the VBA/VB code is?

It is worth noting that due to the given policy I'm strongly discouraged using excellent R or Python libraries for working with data, so I need to stick with MS tools.

Looking forward for any advice, hint or even a loose tip on the topic.

Improve this question
6
  • Welcome to SE! Please take the tour and read How to Ask, then inprove question. Explain more briefly what you are doing,(copy workbooks, sheets or data, validate data, ...?) as you even run an Excel file by opening it. Show example code VBA isn't the fastest, but heavy computing can be done by .Net Com dll's. The slowest thing you can do is running code in workbook that is opened by automation, not by the hostapp (Excel.Application object).
    – ComputerVersteher
    Commented Mar 8, 2020 at 19:28
  • 1
    What garbage collector errors? vba objects lifetime are reference count based . If ref counter is zero, object gets destroyed (maybe you don't remove those refs?). IDE can be improved by rubberduckvba.com! A view to the future (and many great code explanations) can be found in Office-JS & Script Lab. What support are you missing? I miss proper patch testing, the last months, but if removed no problem.
    – ComputerVersteher
    Commented Mar 8, 2020 at 19:31
  • @ComputerVersteher - Thanks for your precious comment. Processes I'm running are typical ETL from both 3rd party sources and CRMs, integration of the data to edible by non-DataScience users and tools (must cover Excel 64- and 32-bit architecture). Most of it runs along data validation, type testing before further analysis, sometimes picture2data replacement, applying business logic to data, pushing outlook notifications, SharePoint data I/O etc.
    – Oskar_U
    Commented Mar 8, 2020 at 20:38
  • @ComputerVersteher - You've mentioned that the slowest thing - which is the case in my situation - is running code from opened workbooks by another ones. Could you eventually provide more details about the solution you're talking about? Thanks in advance!
    – Oskar_U
    Commented Mar 8, 2020 at 20:47
  • 1
    Now explain your resource usage problem. Memory? Time? Usage of external dlls? You import to excel sheets, not database? Excel (Sheet) is not the tool for data edits, its for analyse and evaluate, Use a userform, or easier an Ms Access form (data binding included from scratch!).
    – ComputerVersteher
    Commented Mar 8, 2020 at 22:30

1 Answer 1

Reset to default
3

Powershell is based on the .NET framework infrastructure and runtime, which is pretty efficient and to my own experience often more effective in memory management and CPU utilization than the old COM-based VBA infrastructure. To be precise - it bears more potential of being more effective.

Said that, we cannot tell you if a non-existant PowerShell-based scheduler will be more effective than your current solution, since this would require a crystal ball - we do not know your old solution, and even you do not know your new solution. Writing very inefficient programs is possible in any language and environment, Powershell is no exception from this.

Moreover, it looks a little bit strange to me that in the kind of process you described the scheduler is the actual bottleneck. Usually, I would expect the data processing tasks to be the ones which require most of the performance and memory, whilst the scheduler should be a relatively lightweight program which only orchestrates the other processes.

So if your current scheduler has issues, maybe you should look for the root cause first, which is probably not that it is written in VBA?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.