VBA not dead, yet.

Ivan Bondarenko November 26, 2019 1 Comment

VBA, from what I see, is still used here and there.

Before Power Query had come to Excel I used mainly VBA macros to perform data cleansing and transformation. Hours of coding…

Now I can’t believe I had to fight with IT department to get Power Query addin installed (when it was a standalone add-in. Now it is a built-in part of modern Excel).

Long story short. Now it is Power Query that helps me with data transformation, cleansing, and consolidation.

I built several dozens of workbooks that transform and consolidate data from various sources.

Once extraction and transformation problem is solved comes another thing – how to automate update of all those workbooks? Obviously, I don’t want to open each manually and hit Refresh button every day.

Using VBA, I developed a solution that helps me to update Excel workbooks automatically on schedule – check https://excel.city/excel-automation/ if interested how I do this.

Why not to schedule in Power BI?

“What the hell?” – one may ask: “Create Power BI datasets, schedule refresh in Power BI service, use Gateway when needed, and voila”.

That’s in theory.

Firstly, we need to keep in mind that some organizations prefer to store everything within corporate network only.

Secondly, Excel still can handle more scenarios than Power BI. Or maybe not more, but many scenarios, that Power BI can’t perform. Due to Excel’s extensibility with add-ins and embedded programming language – VBA (Visual Basic for Applications).

Thirdly, far not everything is stored in the databases and accessible from Power Query – the main engine used to pull data to Power BI data models.

Excel add-ins and VBA help to fill that gap quite often for me.

As an example, in Excel I can dynamically change “Named Range” parameters for Power Query queries using formulas or via VBA.

Or, let’s say, you need to pull data sitting in SAP and for some reason not available in corporate BI system – here comes in handy SAP GUI scripting, which can be executed in Excel via VBA.

In the end, through many years, BI solutions come and go. Excel stays!

And if you want Excel to become even better, post your ideas or vote for existing ones at http://excel.uservoice.com.

Notable Ideas

Dear reader, using an opportunity, I’d like to ask you to take a look at the following ideas for VBA improvement.

Vote if feel it will be useful for you too.

Improve Workbook.Query object – add necessary properties – vote here

RefreshAll method has to be improved – vote here

SaveCopyAs for some reason doesn’t save file to OneDrive and SharePoint – vote here

Authentication to SharePoint should not be a problem in VBA – vote here

VBA Trends on Google

What do we know about popularity of Excel, VBA and Access around the globe – let’s ask Google.

Google Trends worldwide for VBA – despite high interest it is gradually declining

Interest over time
Nov 16, 20_
O
May 22. 2016
Nov 26, 2017
Jun 2, 2019

Considering popularity of idea to introduce Python to Excel it is probably worth to learn Python. However, at the time of writing, Excel has only VBA as embedded scripting language.

VBA in United States – slightly declining from 2016

Interest over time
Nov 16.20...
O
Note
May 22, 2016
Nov 26.2017
Jun 2, 2019

Excel and Access Trends

Excel and Access are probably two the most important “containers” of VBA code. So I decided to check what’s happening with interest to these Microsoft products

Microsoft Excel in United States – steady since 2016

Iterest over time
2019
50
Nov 26.2017
25
May 22,

However, Worldwide it is gradually declining

Interest over time
Jun 2019
75
Nov 26.2017
Note
May 22 2016

Interest to Microsoft Access in United States is decreasing over time

Very similar – Microsoft Access Worldwide

Interest over time
Nov 16, 20_
May 22, 2016
Nov 26, 2017
Jun 2, 2019

If you are still using Microsoft Access there must be a good reason for that.

Afterword

Clearly, VBA is not the hottest topic nowadays and it definitely requires modernization to stay competitive. However, even in current form it is still widely used.

To be honest, maybe simpleness of built-in IDE (not so many bells and whistles as in VS Code / Visual Studio) makes it easier to understand and accept by a common folk.

Does it worth to invest time in learning VBA, Excel or Access?

This I can’t tell you. All depends on your situation.

However, automation of tedious tasks in Excel has never been a bad skill.

Filter Excel Function CUBEVALUE Connected to Power BI

Ivan Bondarenko November 26, 2018 No Comments

You might know that we can connect Excel to Power BI using “Analyze in Excel” feature.

If this is something new for you – check out following video (3 min)

By default, after clicking on “Analyze in Excel” you get Excel file with Pivot Table connected to Power BI.

There you can add rows, columns, slicers – everything as usual.

But what if Pivot Table is not what you need?

What if you have pixel perfect dashboard which you need to fill with CUBEVALUE formulas (or maybe you use Think Cell addin)?

And what if you want dropdown list of values in cell instead of slicers (or want filter to be generated via formula)?

Let’s check what we can do.

CUBEVALUE formulas

The easiest way to get CUBEVALUE formulas instead of Pivot Table is to convert Pivot Table to Formulas

That action deletes Pivot Table and replaces all its cells with functions CUBEMEMBER…

…and CUBEVALUE

As you may see, CUBEVALUE is referencing to a Slicer with countries and a cell with a CUBEMEMBER function.

Replace single value slicer with a cell

Everybody likes slicers, me too. However, in certain scenarios it is better to have a cell with value instead.

Why?

(more…)

Tracking expenses using Excel and MS Flow

Ivan Bondarenko October 24, 2018 4 Comments

Do you track your personal/family expenses? If yes, I bet you use a specific software or service for this.

I personally think, there is nothing better than a simple Excel workbook for that task. It allows you to stay as flexible as you want, as simple or as complex as you want.

You can use Power Query to pull data from csv dumps from your bank accounts or currency exchange rates from your favourite financial web site, use Excel formulas to convert currency, Pivot Tables and charts to analyse and visualize data etc.

In this post you will see how you can enter data to your Excel spreadsheet with expenses from your mobile phone.

(more…)

Export PowerQuery query to CSV

Ivan Bondarenko June 1, 2018 No Comments

Recently I found a PowerQuery gem, trick with Java/VB Script that allows to export data from Power Query to CSV without R / DAX Studio / SMS and Registration. However, related with risk. As everything else in our life.

Kudos to user Shi Yang from Stack Overflow who replied to How to write to DATA sources with Power Query?.

Shi proposes to use following code (extended with my comments)

Let
     // reference to a query you wish to export to CSV
    Source = ReferenceToYourTableOrQuery,
     // demote headers to have headers in resulting CSV
     // if you don't need headers, remove Table.DemoteHeaders
     Json = Text.FromBinary(Json.FromValue(Table.ToRows(Table.DemoteHeaders(Source)))),
     // trigger execution of script
  Export = Web.Page("
 var fso=new ActiveXObject('Scripting.FileSystemObject');
 var f1=fso.CreateTextFile('C:/Temp/test.csv',true);
 var arr=" & Json & ";
 f1.WriteLine(arr.join('\n'));
 f1.WriteBlankLines(1);
 f1.Close();
 ")
 in Export

All great, but this method doesn’t work with default settings of Internet Explorer.

(more…)

Publish Excel file to PowerBI using VBA

Ivan Bondarenko April 21, 2017 No Comments

Recently Teo Lachev released a very short but important post about Automation Excel to PowerBI publishing.

In a nutshell: now we have a VBA method PublishToPBI, which allows to publish workbooks to PowerBI.

This is quite interesting scenario for Excel-based Self-Service BI solutions.

I played around with this method, and share with you results of my experiments.

Excel VBA PublishToPBI method documentation

Unfortunately, there is no official documentation yet. At least in the list of Workbook Methods.

Below is my modest attempt.

Purpose

Publish workbook to PowerBI as a report or dataset.

Requirements

workbook must be saved in xlsx or xlsm format.
User with PowerBI license must be signed-in in Excel.
for publishing as a dataset – workbook must have Excel Table or Data Model.

Syntax

expression.PublishToPBI( PublishType, nameConflict, bstrGroupName )

expression a variable that represents a Workbook object.

Parameters

Name	Required/Optional	Data Type / Description
PublishType	Optional	Enum XlPublishToPBIPublishType msoPBIExport = 0, publish workbook as a dataset. Workbook must contain Table Data (range formatted as a Table) or Data Model (aka PowerPivot model). msoPBIUpload = 1 (default) publish workbook as a report, which can be consumed in Excel Online directly in PowerBI.
nameConflict	Optional	Enum XlPublishToPBINameConflictAction msoPBIAbort = 1 (default), workbook will not be loaded to PowerBI if there already exists workbook with same full name (including extension) msoPBIIgnore = 0, ignores existence of report / dataset and creates new one on Upload/Export. msoPBIOverwrite = 2, overwrites existing report / dataset with same full name of workbook. Note: method raises an error if find more than one report / dataset with same full name (e.g. if msoPBIIgnore was used previously).
bstrGroupName	Optional	String Name of target Workspace. If omitted, method loads workbook to “My Workspace” of signed-in user. Note: requires Pro-account, signed-in user must have edit rights in target workspace.

Example

' Creates Dataset from ActiveWorkbook in workspace "Finance Team" (if signed in user has access to it)
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, bstrGroupName:="Finance Team", PublishType:=msoPBIExport
 
' Creates PowerBI Report from ActiveWorkbook in signed in user's workspace
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, PublishType:=msoPBIUpload
 
' Creates Dataset from ActiveWorkbook in signed in user's workspace
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, PublishType:=msoPBIExport

Some Details

(more…)