Best Practices in Excel - Invoking VBA vs. UIPath Data Table Activities

I’m preparing a POC for my first automation proyect with UIYPath, a process in which 90% is formatting, filtering, inserting and removing rows in Excel. I’ve tried doing it using 2 approaches:

  1. UIPath activities + Invoke VBA for the Excel specific operations.
  2. UIPath activities against Data Tables.

I’ve realized that the 2nd approach is 6-7 times slower, so I think the 1st one is the best option if you pursue performance. But what about best practices? Is there any recommendation against the massive use of VBA vs. processing Data Tables?

I’m considering using the 1st approach for the final development, even doing the whole Excel processing using VBA and in UIPath only invoke it, but I want to know that I’m not breaking any best practice rule.

1 Like

If you take the Invoke VBA approach, you would need to create a long infrastructure of Functions and provide extensive coding documentation. Basically, organize it in a way that is re-usable for all processes. Sustainability will ultimately suffer if these things are not done right - as soon as the person who wrote it leaves, nobody ever improves or understands it, so it just gets lost (potentially).

That’s not necessarily true, but I agree that a macro that directly interacts with cells can be more efficient.

There is definitely a market for an Excel activity package that includes all the things you can do with macros (like FillDown, Find, Formatting, etc). I just haven’t seen it, though. I have gotten TypeInto to work very reliably and fast too, so it works as an alternative, but yeah I would prefer the direct approach.

So just to add my final thoughts on this, avoid mixing in script files cause it makes managing re-usability more painful and you end up with copies of the scripts all over. And like I said, sustaining the code requires more work, to be honest.

I’m interested if anyone else has an opinion on this too. To be me, I like projects to be simplistic and easy to understand, and the more pathways you create, the more of a labyrinth it is to transfer the knowledge of the code.

Regards.

1 Like

Thanks Clayton for your answer, I’ll add your advice to the “cons” side in the balance when I’ll proceed to decide the path to be taken.

Regarding the speed, the VBA approach spends 20 seconds and the other one 2 minutes. But I’m just starting with UIPath, sure there are more things I can do in order to shorten the difference. And anyway, 2 minutes is 10 times less than the current manual process, so it has really no much weight in the “pro” side of the balance :slight_smile: .

Thanks again.

Hi
welcome to uipath community
I would prefer the first one only when taking it up comparatively with its speed

because once i had a situation where we need to process the data in the excel and also create charts and tables in different excel with different sheets…i had lot of places to call excel and use them intermittently…at those situations vb script and macros helped a lot
as the activities took a bit more time to do the same especially when looping and processing the data in the datatable obtained from excel

But we can’t neglect few advantages that our activity packages provide which are equivalently or sometimes even more faster with vb script, and that includes
LOOKUP DATATABLE activity
WRITE RANGE activity
REMOVE DUPLICATE ROWS activity
and some of the EXCEL - TABLES accessing activities as well when pivot table are processed

so its completely based on the need and the process we are automating…if that process involves lot of data processing in particular within excel then we can use VB Scripts or if integrated more with other applications as well along with excel then activity package will help us with that as it is easy to pass the arguments from one activity to another

Cheers @frueda

1 Like

Sure, I’ll wait to decide when I’m finally done with the Datatable approach and see how much time it’s eventually spending in the whole process.

Thanks @Palaniyappan.

VBA is restricted to a single core for processing, whereas UiPath can use all resources available so all things equal, UiPath should be quicker. The speed difference you are seeing is likely due to the way you optimized your VBA code vs the unoptimized UiPath code.

In general though, I would echo what @ClaytonM said. At what point is this no longer an RPA project and is instead just a VBA project? Will all the developers who might be expected to maintain the code understand VBA?

For small things interacting with the workbook and most formatting, I will use short and simple VBA code. If it is dealing with data manipulation instead of formatting, or dealing with large transformations, then I will default to UiPath (but may still sprinkle very small VBA code in spots). If you are finding that you are writing many functions or multiple subs that are 20+ lines long, then I think you should step back and ask yourself if you should even be considering this to be a RPA project, or if it should be handed off to someone to handle entirely within VBA instead.

That’s a good point. I’m comfortable writing VBA code, because I’ve always been interested in automating as much as possible anything I do, including Excel workbooks. But maybe other developers are not as comfy as I am with Excel macros.

Said that, what I really did in the VBA approach is what you just said; to use it for “little” Excel tasks, just as filtering columns, formatting and so on. I guess the virtue, as for anything, is in the middle; not everything in VBA code, but not reinventing the wheel in UIPath too if it’s too much complex.

Thanks for your help.

1 Like