Category Archives: MS Office

Save and Get Excel VBA settings

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

Ever had an issue of sharing data (VBA settings) between your workbooks? Not many Excel VBA users know that VBA allows you to easily save and certain variables to and from the Windows Registry, just as if setting permanent VBA settings within your VBA Project. What is more this is already built into VBA that is why you do not have to reference any external libraries or functions. When is this useful:

  • Saving VBA settings of Excel Add-Ins – when using some VBA variables that you would like to save permanently for the user, and you would like to be able to get them at any time. Usually this applies only to saving configurations.
  • Sharing VBA data between workbooks – say you have multiple Excel workbooks which share some simple data fields e.g. certain variables, settings etc. Saving them into the registry is sometimes more convenient especially if the location of all workbooks can change over time.
  • Sharing VBA data between Excel and other applications (MS Office but not only) – technically it should be possible to get these settings from any other MS Office application e.g. MS Access or MS Word.

Source code – Saving VBA Settings to registry

Global myVariable as Integer
Sub GetMyVariable
  myVariable = GetSetting("MyApplication", "MySettings", "myVariable", "0")
End Sub
Sub SaveMyVariable(newValue as Integer)
  call SaveSetting("MyApplication", "MySettings", "myVariable", newValue)
End Sub

Simple right?

See more information on Getting and Saving variables in the Windows Registry at http://msdn.microsoft.com/en-us/library/kb0c3wb9(v=vs.90).aspx

Excel VBA OnTime Function – Make a VBA Alarm Clock

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

One of the many useful features of Excel and other MS Office applications, of which little users know of is the VBA OnTime function. It allows you to execute any Excel VBA procedure after the specified amount of time (delay). Using it recursively allows you to create a recurring / repeating event which lets you schedule periodic VBA Macros to run when working on Excel Workbooks.

Application OnTime Syntax

The syntax of the VBA OnTime function:

EarliestTime
The time (Date variable) when the Procedure is to be executed

Procedure
A string with the name of the Procedure which is to be executed

LatestTime
Optional. This is the latest time when the Procedure should be executed. This allows Excel for some flexibility when prioritizing Procedure execution together with other Excel (or other MS Office applications) Events such as Cut, Copy or Find. It makes sense to use this variable when your user is working on the file, so as not to interrupt any activities

Schedule
Optional. Defaults to True. If True schedules a new OnTime procedure. If False clears a previously set procedure. Useful when you want to cancel a previously scheduled Procedure

Excel VBA Alarm Clock

The code below will schedule the HelloWorld Sub to run in 1 seconds.

From above the following are equivalent:

Wait 1 second and expect a Message Box to open with the message “Hello World!”.

Cancelling Application OnTime

You can also use the Application OnTime function to Cancel Scheduled Procedures.

Example 1: Automatic worksheet recalculate using OnTime

The Timer can come in handy when we need to refresh our worksheet(s) periodically and do not want to do this manually. See the example below:

Let us add this procedure in any VBA module within the workbook:

Next add this to the Workbook in the “Microsoft Excel Objects” section of the VBA project:

That is it! Once you open the workbook the whole workbook will be recalculated every 10 seconds. In order to stop it simply close the workbook or debug the Refresh procedure.

Example 2: Automatic workbook versioning using OnTime

Working on a large Excel solution and afraid that you workbook will crash? Want to have a separate backup copy of your file every now and then. Why not use automatic file versioning then?

Add this code to any VBA module:

Next replace the Refresh function with this:

Voila! Your workbook will be saved as a new version every 10 minutes. Great isn’t? There are probably a ton of ways you can further tweak this to work better e.g. add a pop-up asking to confirm the creation of the next version etc.

Hope that this will prove useful to you! Let me know!

Excel Scrape HTML Add-In now with HTML caching

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

I am constantly extending the Scrape HTML Add-In when I have some spare time. Today’s update adds Caching to the Get* functions.

See the latest version of the Excel Scrape HTML Add-In here:
Excel Scrape HTML Add-In

Why caching?

The primary concept of the Add-In is to reduce any need for writing VBA code. However, the problem with the Get* functions may be that if you specify multiple functions with the same URL there might be some overhead due to having to download the same HTML content just as many times – which might heavily impact performance. In order to solve this issue I have introduced the Cache functionality. If you specify the same URL in more than 1 Get* function then provided that the cache timeout has not passed the HTML content will not be refreshed from the Web but from the Cache. E.g. if you have 2 Get* functions with the same URL and a 60 sec timeout, then only the first call to the Get* function will download the content from the Web, whereas the second one will not – provided that the second call happens before the 60 sec timeout ends. You can change the setting at any time in the “Cache settings”.

Cache Settings
Cache Settings

Running Excel VBA from VBscript

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

Today’s post is going to be very niche. But as I am often bored lately and playing around as a Data scientist in the Kaggle contests I occasionally run into these strange places where I need to make some peace of ultra-efficient code and needing to automate as many things possible.

Run VBA from VBscript

So I ran into the issue of having to run some Excel macros automatically from the Windows command line (cmd). I had some Excel file which was much easier to process in VBA than in R (this reminds me to have to learn Python soon). But then again I develop my machine learning models in R and not Excel. So I needed a way to execute Excel macros from R… to do some of the dirty work. How to do that? Fortunately R has the Shell command which invokes the Windows shell (cmd).

VBscripts are executable text files with the vbs file extension. They are supported natively by Windows and can be executed without MS Office. VBscript is almost identical to VBA hence most VBA scripts can be exported to simple VBscript files.

Want to learn how to make an App without Excel using VBscript? Read this post on HTA

The only thing left was to write a VBscript which would allow you to run an Excel macro (run VBA from VBscript). Here is an example:

'This runs the macro below
RunMacro

'The RunMacro procedure
Sub RunMacro() 
  Dim xl
  Dim xlBook      
  Dim sCurPath
  path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
  Set xl = CreateObject("Excel.application")
  Set xlBook = xl.Workbooks.Open(path & "\Workbook.xlsm", 0, True)      
  xl.Application.Visible = False
  xl.DisplayAlerts = False     
  xl.Application.run "Workbook.xlsm!Module.RunMacro"
  xl.ActiveWindow.close
  xl.Quit
  Set xlBook = Nothing
  Set xl = Nothing
End Sub 

Cool right? I was thinking what use cases can be out there for using this code. These are a few I came up with:

  • Scheduled tasksCreate VBscript to run on system start-up or other similar events. This can be useful if e.g. you are an analyst which needs to refresh data on a daily basis. Running the Excel macro tasks automatically will save you a lot of time.
  • Running Excel macros from other applications – this is basically the use case I quoted above. However, keep in mind that running shell (cmd) Windows commands if possible virtually from any platform e.g. Java, C#, ASP.NET etc.
  • Parallelism – Excel does not allow you to utilize more than 1 thread when running Excel macros in a workbook. Ok. But what if I split my workbooks and run them from VBscript? Running all these workbooks in separate processes from VBscript will indeed be an elegant approach. Much cleaner than opening quadrillion workbooks… Although please do not do this. Excel is not meant to accomplish such tasks – use R or Python or even C#

Excel Scrape HTML Tool added to the Scrape HTML Add-In

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

As I mentioned I am planning to extend the Scrape HTML Add-In with additional tools and functionalities. Scraping HTML content should not require any VBA coding – this rule is guiding the further development of this tool.

See the latest version of the Excel Scrape HTML Add-In here:
Excel Scrape HTML Add-In

Adding to the toolbox I would like to introduce you to the Scrape HTML Tool. The Get* functions (e.g. GetElementByRegex) which I posted earlier are really useful when you are making a solution which can be reused e.g. scraping regularly posted online data etc. I suppose, however, that sometimes there is only a need to scrape some content once but preferably in a structured manner or only some subsets of the content.

The Scrape HTML Tool

The Scrape HTML Tool
The Scrape HTML Tool

The tool comes in handy when you want to quickly scrape all items of a certain type (matching a certain regular expression). It comes with several predefined scraping regular expressions e.g. scraping URLs and img src properties. However, these examples are just to start you off with building your own patterns/expressions.

See this video on how the Scrape HTML Tool can help you:

Let me know if this tool is useful to you and if you see any need of extending it!

I am also planning to post some a simple tutorial or something to show more elaborate examples of scraping/downloading HTML content from the web. Information is power – it is time to make usage of data more simple.