First touch of VBA

Yesterday my boss asked me: “Are you able to work with Office?”
Since I’m not a total Office-newbie I answered: “I’m able to use it for the things I need.” Very diplomatic answer, but not that I would have helped me anyway :).

A few minutes later I got myself a task to parse and transmute some raw data in an Excel file. Transpose lines into columns, do some calculations with dates, … pretty easy stuff. With the only problem that I have never ever programmed a single letter of VBA. And the last time I saw a glimpse of VB pre was on a sunny day 5 years ago. But hey! We’re here to learn new things.

Some useful things to remember for later use (if we’d ever had to use VBA again):

Find the last used cell in a column of data:


Add Date and Time part together and assign to the value of a single cell:

Cells(x, y).Value = DateValue(Cells(Row, 1)) + TimeSerial(Cells(Row, 2), Minutes, 0)

Avoid flickering (don’t forget to set on true on the end of the method):

Application.ScreenUpdating = False 'Rest of your pre Application.ScreenUpdating = True

Speed up your method by disabling calculation, very useful if you’re running this method on large amounts of data:

Application.Calculation = xlCalculationManual 'Your pre Application.Calculation = xlCalculationAutomatic 

Compare Time parts of 2 cells in different formats containing day+time or time values:

TIME(HOUR(times),MINUTE(times),SECOND(times)) >= TIME(HOUR(RxCy),MINUTE(RxCy),SECOND(RxCy)) 
Licensed under CC BY-NC-SA 4.0; code samples licensed under MIT.
comments powered by Disqus
Built with Hugo - Based on Theme Stack designed by Jimmy