Tag Archives: vbscript

CPU Usage

Measuring CPU usage in Excel VBA (and other performance metrics)

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.67 out of 5)
Loading...

VBA easily allows you to get your hand on your PCs performance metrics which often can prove useful when wanting to do some performance statistics. Having been playing with the VBA Multithreading Tool I wanted a way to see how well all my processor cores are utilized – especially when wanting to optimize the amount of threads needed to be spawned.

Windows Management Instrumentation – the single source of your performance metrics

cpu usageWindows Management Instrumentation (WMI) is the infrastructure for management data and operations on Windows-based operating systems. It is useful when wanting to extract performance data, when automating administrative tasks both on local and remote workstation. Read more here.

WMI can be used in all Windows-based applications, and can be easily accessed using VBA, VBscript, C# or VB.NET.

Extracting performance data in Excel VBA

Walking you through this process I will want to extract CPU usage measured as percentage processor time.

Fortunately, Microsoft has issued a fantastic tool for generating code needed to extract any needed performance metrics using WMI.

Download the WMI Code Creator and select your metrics

Although you don’t need to it is much easier to utilize the Microsoft WMI Code Creator to generate the necessary performance metrics code e.g. CPU usage.

Use the link below to download from Microsoft:
Link to Microsoft WMI Code Generator
Open the attached WMICodeCreator.exe file to open the WMI Code Creator window. You should see something like this:

WMI Code Creator screenshot
WMI Code Creator screenshot

To configure it properly:

  • Select Visual Basic Script from the Code Language drop down menu.
  • Leave the namespace as rootcimv2 if you want general workstation performance metrics e.g. CPU usage, memory usage etc. Feel free to read more on the WMI Reference.
  • Select an appropriate class for your desired metrics. E.g for CPU usage I will select Win32_PerfFormattedData_PerfOS_Processor.

The code should generate automatically. You can execute it to see if it works by clicking Execute code.

Copy the code to your VBA Project and adjust it to VBA

Now just copy the above mentioned code into your VBA project. VBA and VBscript are very similar programming languages (or scripting languages as preferred by others). VBA does not identify the Wscript.Echo method which in VBA is represented by the Debug.Print equivalent. See an example below of the VBscript vs. VBA code.

VBscript (original from WMI Code Creator):

strComputer = "." 
Set objWMIService = GetObject("winmgmts:\" & strComputer & "rootcimv2") 
Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM Win32_PerfFormattedData_PerfOS_Processor",,48) 
For Each objItem in colItems 
    Wscript.Echo "-----------------------------------"
    Wscript.Echo "Win32_PerfFormattedData_PerfOS_Processor instance"
    Wscript.Echo "-----------------------------------"
    Wscript.Echo "PercentProcessorTime: " & objItem.PercentProcessorTime
Next

VBA (adjusted):

strComputer = "." 'Local machine, can be adjusted to access remote workstations
Set objWMIService = GetObject("winmgmts:\" & strComputer & "rootcimv2") 
Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM Win32_PerfFormattedData_PerfOS_Processor",,48) 
For Each objItem in colItems 
    Debug.Print "-----------------------------------"
    Debug.Print "Win32_PerfFormattedData_PerfOS_Processor instance"
    Debug.Print "-----------------------------------"
    Debug.Print "PercentProcessorTime: " & objItem.PercentProcessorTime
Next

Example: Measure CPU usage in VBA

Using WMI and the code above I quickly coded an Excel VBA project workbook that allows you to measure CPU performance in real time (1-2 second intervals :)).

The macro works as follows:

  • When hitting start the macro generates as many cores as identified using WMI
  • Every 2 seconds the metrics will refresh showing both CPU percentage processor time and the core utilization

Screenshot below:

CPU Usage: Excel Workbook
CPU Usage: Excel Workbook

Download the example

Feel free to download the code sample below:

programming languages

Programming languages analysts should learn

1 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 4.63 out of 5)
Loading...

Programming is a skill often associated only with Developers and not so much with analysts or data scientists. Why learn to code when you have tools such as Excel with it’s PivotTables, filtering and formulas. On the other hand, when thinking about analytic tools it seems like Tableu, ClickView, SAS or IBM SPSS is the way to go.programming languagesThese tools are great when working with Big Data. But most of us work with data sets counting not more than a few hundred thousand records. So why use a sledgehammer to crack a nut? Programming languages such as VBA, SQL or R let you squeeze much more of Excel and traditional tools .

VBA (Visual Basic for Applications) (MS Office)

USEFULNESS:
EASY TO LEARN:

Excel is the entry-level tool for most analysts and data scientists, allowing for easy data crunching tasks. In most cases formulas and PivotTables suffice in everyday analytics tasks. However, VBA extends Excels capabilities almost without limits – allowing for creating complex algorithms, extraction of data from various data sources, web scraping and load others. VBA owns it popularity to Excel beings its programming and testing environment at the same time. Write a piece of code and see it run in seconds.
If you want to progress as an analyst / data scientist – learn VBA.

One other perk to learning Visual Basic for Applications (VBA) is that the syntax is identical to VBscript. If you are not familiar – VBscript is a Windows scripting language working alongside Windows Batch Scripting (used in BAT files). VBscript can be used to write Windows scripts that don’t need Excel or MS Office installed on the workstation. VBA code can often be copied identically and used to create VBscript (with some differences like VBscript cannot include data types e.g. “Dim str as String” would raise an exception).

Example uses of VBA

programming languages vbaWhen VBA can prove useful?

Well there are ample examples to consider below. One of the most often proven ways to start is by using the Record Macro feature on the Developer tab.

  • Automate mundane Excel tasks e.g. formatting, editing etc.
  • Creating new custom Excel functions (UDF)
  • Implementing algorithms or complex formulas e.g. complex computations
  • Extending capabilities e.g. working with files, connecting with Internet resources
  • Creating Excel-based apps with User Forms and interaction

Code example

Here is a simple VBA script to show a simple message box with Hello World!

Sub HelloWorldProcedure()
  MsgBox "Hello World!"
End Sub

Where to start?

SQL (Structured Query Language) (RDBMS)

USEFULNESS:
EASY TO LEARN:

SQL lets you unleash the potential of database development. While Visual Basic (VBA is an implementation of Visual Basic) is a general-purpose scripting programming language, SQL is a special-purpose programming language- aimed at running queries and CRUD (Create, Read, Update, Delete) operations on relational databases such as MySQL, MS SQL, Oracle, MS Access etc. SQL is ubiquitous in the data world.

What is often not appreciated about SQL is that it supported by MS Office. Microsoft developed a database engine (JET.OLEDB and the newer ACE.OLEDB), that supports SQL, and can be used to query several Microsoft products such as MS Excel and MS Access (but also other formats like text files). Why is this important? SQL let’s you carry out many operations much easier and quicker e.g. merging Excel worksheets, filtering Excel worksheets on multiple complex conditions, linking Excel Workbooks with other Workbooks or Access databases. If you wanna learn more check out my Excel SQL Add-In

When will you want to reach out for SQL? Whenever you are analyzing/processing large data sets. Excel will typically start falling apart when processing 1000k record databases with the use of formulas, PivotTables etc.

Feel free to check-out this SQL Quick start learning video for Beginners:

Example uses of SQL

programming languages sqlSQL currently rules the world of data, even though NoSQL databases have enriched the database mix. Although many have announced the end of SQL times to be near, recent developments in approaches to programming such as micro-services and the overall ease of use and integrity of SQL database seem to negate these forecasts. Most datasets sit on SQL databases (with MySQL in the lead) so as an analyst sooner or later you will benefit from knowing how to query these datasets directly looking for insights and when building reports.

But does SQL come in handy when you are not dealing with large datasets or structured relational databases? Sure both in Access and Excel SQL can and should be used when dealing with data tables. Here are some of the main aspects SQL is valued for:

  • CRUD: SQL enables Create, Update, Update, Delete operations on relational databases (relations of tabular data sets)
  • Queries: SQL allows you to create queries based on multiple tables of data
  • Performance: SQL is fast and efficient for processing databases including large amounts of data

Code example

List all people at the age above 30, and with red eyes from a hypothetical People table :

SELECT * FROM People
WHERE Age > 30 AND EyeColor = "Red"

Where to start?

R programming language

USEFULNESS:
EASY TO LEARN:

R is a powerful programming languages for statistical computing and graphics. It’s one of the top programming languages used by data scientists (along with e.g. Python) out there (at least on Kaggle as I am aware). R has a moderately steep learning curve as its not a typical programming language, and was historically created by statisticians not developers, although it certainly is worth the effort. R focuses on operations on data frames (tables) and its whole design is oriented around this. What is more important in the case of R is its vast CRAN library of packages that provide additional statistical, graphics and machine-learning capabilities. R is also the go to language when processing so called Big Data. It make slicing and dicing data easy and pleasant.

Another nice thing about R is that it comes with a software environment (RStudio) similarly like you have the Visual Basic environment packed together with MS Office applications. This makes starting to work with R much easier, especially for beginners.

Here is a nice zero-to-hero learning series on R:

Example uses of R

Typically R is considered a great tool for the below. However, keep in mind that you can almost extend R with any capability by downloading the right package for R’s CRAN library e.g. Web Scraping etc.

  • Data exploration e.g. plotting statistical data relationships
  • Statistical data analysis
  • Data cleansing – correcting, formatting series of data e.g. CSV and text files
  • Machine-learning algorithms

Code Example

A simple function that introduces itself:

hello <- function( name ) {
  sprintf( "Hello, %s", name );
}
hello("World")

Just how easy it is to load CSV/text file data with headers:

data <- read.csv("data.csv", header = TRUE)
x <- c(1,2,3,4,5,6)   # Vector of numbers
y <- x^2  #Result: c(1, 4, 9, 16, 25, 36)

Where to start?

Here are some useful links to get you started with the R programming language:

Other programming languages worth mentioning

Now for some other honorable mentions worth exploring:

Python

USEFULNESS:
EASY TO LEARN:

Python is one of the most popular script programming languages out there currently. Especially valued for its ease of use and for being applicable to many environments and scenarios. Python is great for writing quick scripts to process files, data or automate easy tasks. It doesn’t have the overhead of object oriented programming languages and is very flexible. I personally use Python most often for Web Scraping or for automating Linux/UNIX tasks.

If you already know VBA and Excel is your primary working environment, Python might be of less value to you.

C#

USEFULNESS:
EASY TO LEARN:

C# is Microsoft’s flagship programming language and a rival to the all-popular Java. C# is a typical Object Oriented programming language and it’s primary software development environment is Microsoft’s Visual Studio. C# is very useful when making efficient and optimal solutions. Visual Studio is a big asset of C# as programming in VS is pleasant and easy. When to use C#? When you need a Graphical User Interface to put a face on your solution and when you want to “click out” most of your code instead of writing it.

hta example

Like VBA? You will love HTA! (HTA example using VBS)

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Comfortable with VBA? Your clients/users need VBA solutions but want them to be lightweight GUI, not cumbersome Excel Workbooks with Macro restrictions? Well why not try HTML Application (HTA)? What’s HTA? It’s a simple HTML application embedded with Visual Basic script or Javascript, or even both if needed! Don’t see the opportunities yet? Imagine a cool Web GUI (feel free to leverage Jquery or other libraries) embedded with a cool feature you already have programmed in Visual Basic. No need to install or open Excel! What is more HTA applications are simply text files with a HTA extension, hence, anyone can easily open and edit them in a simple text editor. Neat right? Interested – read on.

From Zero to VBA Hero

Today I wanted to showcase a simple HTA example. There are tons of VBA developers out there currently. These are often persons who started their developer journeys as analysts or simply Excel powerusers. At some point in time Excel native features are not sufficient to tackle some complex tasks and drive users to learn VBA. It is quite an easy step often as, it is important to remember that Excel has 2 important advantages over other programming languages:

  1. It is available almost on any Windows workstation (as most have MS Office installed)
  2. It features a built in development environment.

Without having to install any additional software, any analyst or other corporate person can use VBA to achieve automation of certain tasks. In a series of posts I will want to encourage some VBA devs to explore some alternatives and new programming possibilities.

I know VBA and can develop anything in MS Office! Why explore other languages, approaches?

From my experience many VBA developers refrain from learning other programming languages, noticing that basically they build roughly any solution or automation resorting only to VBA. This is a common trap due to which many MS Office-based monster-complex solutions are developed which should not have seen the light of day. I have seen many monsters… from complex BI models (calculations running for hours) to … complex databases shared by multiple users at the same time (oh the horror!). Although all these solutions were quite amazing they pushed the limits of VBA more than they should. I won’t ponder to much on the reasons for these solutions to come to existance. Rather I would like to show some alternatives.

VBA developers start exploring new possibilities with HTA!

In today’s post I wanted to encourage VBA dev’s to make their first step out of their VBA caves and to leave Excel or any other MS Office environment they use on their daily basis. I know this comes as a shock for some so I promise we will stick with Visual Basic for now.

So now let us say we want to build a regular desktop app not one hosted with an Excel or Word application. Assuming we do not want to move to more complex solutions like .NET or Java, VBA devs are encouraged to explore HTA.

What is HTA?

HTA is the abbreviation for Html Application. See the Wiki page for more details. In short, however, it is simply a HTML file with a “.hta” file extension that can runs on Internet Explorer, embedded with scripts. Well now, some might say, is it simply a HTML file then? Well, hta file have elevated security privileges allowing them to run like regular desktop apps!

HTA can run like desktop applications being embedded in Internet Explorer instead of e.g. Excel or Word in case of VBA. Additionally as these are HTML files you can build a rich user interface and a much more pleasant user experience. But let’s jump right in!

HTA Example: Hello World!

Let’s start by creating an hta file. Create a text file and change the extension to .hta

HTA file icon
HTA file icon

Next let’s insert some Hellow World code in there:

<html>
<head>
<HTA:APPLICATION ID="HelloExample" BORDER="thin" BORDERSTYLE="complex" maximizeButton="yes" minimizeButton="yes" />
<script type="text/vbscript">
  Sub Hello()
	MsgBox "Hello"
  End Sub
</script>
<title>Blog.tkacprow.pl: HTA Hello World!</title>
</head>
<body>
<table width="100%">
	<tr>
		<td>
			<!--Your content here!-->
			<H1>Hello World!</H1>
			<input type="Button" onClick="Hello()" value="Hello" />
		</td>
	</tr>
</table>
</body>
</html>

Now double click and that’s it!

HTA Example: Hello World!
HTA Example: Hello World!

Simple huh? Now to analyze the code…
If you are familiar with HTML you will notice that the HTA:APPLICATION clearing does not belong in a regular HTML file. This tag defines the HTA application and includes params that let you specify how the application window will look like.
Here you will find more on the HTA application params: HTA Application Params

Notice also the script tag:

<script type="text/vbscript">
  Sub Hello()
	MsgBox "Hello"
  End Sub
</script>

This procedure is linked to the HTML button so when clicked you will see a simple Hello message.

Accessing Excel files and resources

As HTA files have elevated rights I like to think of them as Vbscript files extended with a user interface. Bascially all Vbscript and Javascript commands are valid in HTA. Let us see a simple example that exercises some of these rights – like accessing Excel files.

I added a simple Excel file to the directory:

Excel and HTA
Excel and HTA

This Excel file contains a single data cell:

Now let us add a piece of code to the HTA file to access the data cell. Let’s replace the previous script tag with this:

<script type="text/vbscript">
  Sub Hello()
    Set objShell = CreateObject("WScript.Shell") 
    myCur = objShell.CurrentDirectory 
	dim excelApp, excelWb, excelVal
	set excelApp = CreateObject("Excel.Application")
	set excelWb = excelApp.Workbooks.Open(myCur & "Test.xlsx")
	excelVal = excelWb.Worksheets(1).Range("A1").Value
	excelWb.Close
	excelApp.Quit
	MsgBox excelVal
  End Sub
  Sub ExitForm()
	Close
  End Sub
</script>

Now when we open the HTA and hit the Hello button we will see the contents of the data cell.

Modified HTA accessing Excel file
Modified HTA accessing Excel file

All this is just a drop in the ocean of the possibilities of using HTA.

Summary

HTA extends new possibilities to VBA developers and provides them we a simple means of making desktop applications in Windows without embedding them in Excel nor other MS Office applications. In my opinion HTA encourages VBA developers to also explore new programming languages and scripts as with HTA you need to learn HTML and probably some Javascript. Over time you will probably learn CSS and start using some cool Javascript libaries like jQuery, AngularJS. HTA enables you to create rich desktop applications while also giving you the safe-haven of being able to keep using VBscript (almost identical to VBA).

Expect more on this topic soon :). Let me know what you think!

vba multithreading tool

Excel VBA Multithreading Tool

1 Star2 Stars3 Stars4 Stars5 Stars (12 votes, average: 4.42 out of 5)
Loading...

The VBA Multithreading Tool, from AnalystCave.com, is a free and open source tool (VBA) for enhancing your VBA Project with Mulithreading capabilities. It was designed to make VBA multithreading quick an easy and to minimize the complexity of your VBA code. VBA is natively singlethreaded hence will utilize only a single logical processor core. Why waist your processors underutilized capacity and significantly speed up VBA code execution time?

vba multithreading tool
Mulithreading VBA: Mulithread Excel running in parallel

Until now I have posted quite a few posts on the topic of multithreading in VBA. Let’s admit – there is no such thing as “native” VBA Multithreading. This is the closest I got to “native” VBA Multithreading. However, I considered one of other of my similar posts on this topic as a good enough alternative to achieving multithreading to VBA – by using VBscript and VBA worker threads.

Having compared at least 3 methods for achieving multithreading in VBA (EXCEL: Multithreading VBA – VBA vs. VBscript vs. C#.NET) I came to the conclusion that the VBA worker threads can be a decent alternative to C#.NET COM / DLLs. Having said that, I decided that it might make sense to make it easier to leverage these multithreading capabilities in VBA. That’s why I created the VBA Multithreading Tool based on the concepts of the C# Parallel Class.

Example 1: VBA Multithreading For loops

For loops are very common cases for multithreading – as, usually, the result of each iteration is independent to the remaining. I would like to show you how you can use the VBA Multithreading Tool to run a loop in separate parallel threads. Let’s say we have a simple procedure as shown below. This procedure simply divides two numbers in a loop. Each iteration is independent of the other and there is no order in which we need to calculate each iteration. Notice the arguments of this procedure, as they will be required for multithreading later on.

Sub RunForVBA(workbookName As String, seqFrom As Long, seqTo As Long)
    For i = seqFrom To seqTo
        x = seqFrom / seqTo
    Next i
End Sub

We can now execute this code like this in a standard single threaded VBA manner:

Sub RunForVBASingleThread()
    RunForVBA ActiveWorkbook.Name, 1, 1000
End Sub

Now let’s use the VBA Multithreading VBA Tool to do the same thing within 4 separate threads.

Sub RunForVBAMultiThread()
    'Declare the Parallel class object
    Dim parallelClass As Parallel
    'Initialize the Parallel class object
    Set parallelClass = New Parallel
    'Declare how many threads are to be created
    parallelClass.SetThreads 4
    'Call 'Run the for loop in 4 threads synchronously
    Call parallelClass.ParallelFor("RunForVBA", 1, 1000)
End Sub

4 lines of code to make this procedure multithreaded :). Easy right? As I mentioned earlier the ‘RunForVBA’ procedure has to have the 3 arguments for the ‘Parallel’ class to be able to run it in separate threads.

How does Multithreading For loops work?

Multithreading For loops
Multithreading For loops

The ParallelFor procedure creates as many separate Excel threads as required and blocks the Master Excel Workbook’s execution until all threads have finished and closed. Each thread is in fact a copy of the Master Excel Workbook therefore all VBA function or procedures which you will be multithreading will have access to exactly the same data as the Master Excel Workbook.

VBA Multithreading Tool: For loop multithreading procedures

Considering For loops the following funtions and procedures are available:

  • SetThreads(threads As Long) – set the amount of parallel threads to be used
  • GetThreads() – get the amount of parallel threads to be used
  • ParallelFor(macroName As String, seqFrom As Long, seqTo As Long) – execute the macro macroName over a sequence of seqFrom to seqTo. The macro macroName should have the following arguments (workbookName As String, seqFrom As Long, seqTo As Long)

Example 2: Asynchronously invoking VBA procedures

VBA synchronously blocks the execution of any called functions or procedures (except for COM calls – but they are not native VBA). This is because it runs in a single thread. By leveraging the VBA Multithreading Tool we can however asynchronously call functions and procedures to run them in the background while we proceed with the execution of other macro code. When we are ready to wait for the async background thread to finish with can call AsyncThreadJoin to Join macro execution on this thread. Alternatively we can also call as many asynchronous threads as we like and not bother waiting for them.

To exemplify this functionality let’s again consider a similar procedure as before. This time the arguments are not important – although it is usually recommended to pass the master Excel Workbook name to an async thread if we want to somehow save the outputs of the threads to the master Excel Workbook.

Sub RunAsyncVBA(workbookName As String, seqFrom As Long, seqTo As Long)
    For i = seqFrom To seqTo
        x = seqFrom / seqTo
    Next i
End Sub

But now instead of running it in parallel threads let’s invoke it asynchronously and continue executing the macro. We will wait for the procedure to end by the end of the procedure.

Sub RunForVBAAndWait()
    Dim parallelClass As Parallel
    Set parallelClass  = New Parallel
    'The line below will not block macro execution
    Call parallelClass.ParallelAsyncInvoke("RunAsyncVBA", 1, 1000)
    'Do other operations here
    '....
    if parallelClass.IsAsyncRunning then ... 'Check if Async thread is still running
    '....
    'Now let's wait until the thread has finished
    parallelClass.AsyncThreadJoin
End Sub

How does asynchronous VBA procedure invoking work?

Async procedure invoke
Async procedure invoke

The ParallelAsyncInvoke procedure is non-blocking therefore allows you to execute other macros within the Master Excel Workbook and choose whether you want to, as some point, wait for it to finish execution or not.

VBA Multithreading Tool: Asynchronous VBA invoking procedures

The following funtions and procedures are available for async invoking:

  • ParallelAsyncInvoke(macroName As String, Optional arg1, … [up to arg 6]) – execute asynchronously the macro macroName with the following arguments arg1, arg2 etc.
  • AsyncThreadJoin – wait for the VBA worker thread to finish

Example 3: Saving data to the master Excel Workbook

All examples above assumed that the code was executed in separate threads without any means of the threads’ outputs being saved to the master Excel Workbook. You have probably noticed that this is actually crucial as otherwise you will not benefit from calculations happening in the void without being able to access their results. I would like to show you how easily you can save your results from the separate threads to the master Excel Workbook while you are keeping the workbook open and even making changes to it!

Again let’s consider the similar procedure as shown above. This time, however, notice the changes:

Sub RunForVBA(workbookName As String, seqFrom As Long, seqTo As Long)
    For i = seqFrom To seqTo
        x = seqFrom / seqTo
    Next i
    'Save the last division operation 'x' to cell 'A1' on worksheet 'Sheet1' in the master Excel Workbook.
    ParallelMethods.SetRangeToMaster(workbookName, "Sheet1","A1", x)
    'Do the same but this time the procedure will reflect the changes made to the thread workbook
    'automatically to the master Excel Workbook based on the provided Range
    Dim tempRange as Range
    Set tempRange = Range("A1")
    tempRange.Value = x
    ParallelMethods.SaveRangeToMaster(workbookName,  tempRange)
End Sub

Again quite simple. The threads will save their outputs immediately to the Excel master Workbook – as long as it is open and in use.

Summary

I hope the VBA Multithreading Tool will become a useful for you. The Tool makes multithreading almost just as easy as in any other languages that allow for multithreading natively.

Download

You can download the Tool (complete source code) here:


If you prefer to download this as an add-in (the warning won’t appear) please follow this link: here.

Next steps

Want to learn how to add multithreading using C#.NET?
EXCEL: Multithreading VBA using C#.NET
Want to learn how to add multithreading using VBscript?
EXCEL: Multithreading VBA using VBscript
Want to learn how to add multithreading using VBA via VBscript?
EXCEL: Multithreading using VBA via VBscript
Want to see how all multithreading approaches compare?
EXCEL: Multithreading – VBA vs. VBscript vs. C#.NET

invoke vba

Invoke VBA functions and procedures from strings

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 3.50 out of 5)
Loading...

invoke vba
First let me start with explaining what does it mean to invoke VBA functions and procedures from strings. Then I will follow with an example. Lastly I will conclude this topic showing how you can add arguments.

Invoke VBA functions and procedures by string name

Normally you would invoke a VBA function or procedure like this:

Sub ShowMessage(msg as String)
   MsgBox msg
End Sub

Sub Main
   Call ShowMessage("Hello!")
End Sub

In the above example you are invoking the ShowMessage procedure directly from VBA. This is the usual and efficient approach. Let’s now say that you have 2 alternative VBA procedures you would like to call depending on a certain argument you receive like this:

Sub ShowMessageAlert
   MsgBox "Alert: Watch out!"
End Sub

Sub ShowMessageWarning
   MsgBox "Warning: Look around your back."
End Sub
You may want to you invoke both methods in different circumstances e.g

Sub Main(msgType as String)
   if msgType = "Alert" then ShowMessageAlert
   if msgType = "Warning" then ShowMessageWarning
End Sub

I know the examples above are too simple, but what if you have now much more of these similar methods which differ only in name e.g. suffix, prefix? Well you can invoke them by their name (using a string with the proc., func. name) like this:

Sub Main(msgType as String)
   Application.Run "ShowMessage" & msgType
End Sub

Neater right? Alternatively you could have achieved the same using multiple if statements or a select statement making the code much harder to read.

Invoke VBA functions and procedures with arguments

See the example below for invoking VBA functions and procedures with arguments:

Sub ShowMessageAlert(msg as String)
   MsgBox "Alert: " & msg
End Sub

Sub ShowMessageWarning(msg as String)
   MsgBox "Warning: " & msg
End Sub

Sub Main(msgType as String, msg as String)
   Application.Run "ShowMessage" & msgType , msg
End Sub

Invoke VBA functions and procedures from VBscript

Now this is where invoking VBA really comes in handy. Imagine you want to run an Excel macro without having to open the Excel workbook and run the macro manually. It might be more useful to create therefore a VBscript which would do this for you (file extension “vbs”). The code below will open (invisible in the background) the “Book1.xlsm” Excel workbook which is located in the same working directory as the VBscript file and execute macro “MyMacro”. Then it will close the workbook and quit.

dim xl, xlBook, path
path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
Set xl = CreateObject("Excel.application")
Set xlBook = xl.Workbooks.Open(path & "Book1.xlsm", 0, True)      
xl.Application.Visible = False
xl.DisplayAlerts = False     
xl.Application.run "Book1.xlsm!Module.MyMacro"
xl.ActiveWindow.close
Set xlBook = Nothing
xl.Quit 
Set xl = Nothing

Hope you find invoking VBA from strings useful!