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:
ActiveSheet.Range("A1").End(xlDown).Select
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))