Caveat: This is how I go about problem-solving in general and programming specifically, which is probably pretty pedestrian and real programmers would have far more elegant solutions, but… it works. I’m running Excel/MS Office 2000 in Virtual Box/Windows so if some of my notes seem a bit OS-schizophrenic, this might be the reason.
When I started getting into writing macros and later routines and functions, I was working in Microsoft Excel 2000 and the VBE. As a development environment, I still prefer the MS VBE over Libre-Office’s Visual Basic Editor, generally speaking. I did want to go with LibreOffice’s flavour of VBE in this discussion but found it too lacking in features for the time being, so I’ll use the Excel 2000 VBA. The features I’m used to include “Autofill Members”. At this point, Libre-Office doesn’t appear to have this, but Gambas does, as well as some other things that make it compare favourably to what I consider a “mature” product: Microsoft Excel.
First, let’s decide what this function is going to do. On the last page I made a few assumptions.
- Any date-type data gleaned from a US-based site will be in month-day-year format of some sort. Looking at some of the dates, this assumption proves true: consider ’11/17/10’… there is no 17th month.
- If one date is in this format, all will be.
- All transactions will have happened after the year 2000.
On the spreadsheet, the date column is actually save in text versus date data type, which is going to make it all a bit easier to do. Trust me, if you live outside the US, you will find Microsoft’s nationalistic myopia incredibly frustrating and arrogant… this is typically expressed in forcing date formatting according to the month/day/year nonsense.The only way I have been able to get it to behave — and even then, with a very large stick and threatening demeanour — is to work on date information as string data… and believe me, I’ve struggled with this for days. Tried custom formatting in the column. Tried:
txtDate.Value = Format(Date, "dd/mm/yyyy")
It is probably the single most frustrating thing about Microsoft products: nothing exists outside the US of A, according to Microsoft.
So, essentially we’re going to swap the numbers before and after the first ‘/’ (forward-slash) and then add a ’20’ in front of whatever is after the second ‘/’.
To get to the VBE in Excel, press [Alt][F11]. To start a new code module:
Menu -> Insert -> Module
I always put the statement:
Option Explicit
at the top of all code in Excel. What this does is make sure all your variables are defined (and spelled correctly, since ‘myVar’ is not the same as ‘myvar’ is not the same as ‘MyVar’)… this is a very practical feature and really a good practice to follow: it will prevent a lot of grief later. You won’t need to include this line in Gambas: the environment will always behave as if this statement exists (which is a good thing).
Next comes the code for my function, which I’m going to call dateConv(). I want to make it available to the entire workbook, hence the ‘Public’ in the name definition:
Right.
So, the code is going to act on values of a spreadsheet: the ‘ItemsPO’ sheet. We’re going to treat that worksheet as an object, which exposes a lot of properties and methods of worksheets, which will make our work a lot cleaner and easier to do. You’ll see how this works as we go. We’ll define a placeholder for that object and call it ‘oSheet’. I tend to use a character in front of the name that tells me what sort of data type that name represents, so ‘o’ for object, ‘b’ for boolean, ‘i’ for integer and so on.
You define variables — which are in my mind sort of placeholders — with the ‘Dim’ keyword, so:
Dim oSheet as Worksheet
You’ll notice that as you start typing the word ‘Worksheet’ a box will pop up with suggested object types (and there’s heaps of those!)… this is really a nice feature, not only for what it shows, but also for what it doesn’t, so if you try to type something that isn’t supported, you’ll know because it isn’t in the list.
I’m also going to set up variables for the string fragments of the entry in each field: for day, month and year (in string format) and then, for the for-next loop (which is how I’m going to step through the column), i as integer.
So, here is the code so far:
Option Explicit
Public Function dateConv()
Dim sfDay As String, sfMon As String, sfYear As String, sfResult as string
Dim oSheet As Worksheet, i As Integer
Set oSheet = Worksheets("ItemsPO")
With oSheet
End With
Set oSheet = Nothing
End Function
All the fun stuff is going to happen between the “With oSheet” and “End With”.
Oh, I tend to try to do all my housekeeping beforehand and clean up my objects after I’m done with a function, which is what “Set oSheet = Nothing” is about.
Another incredibly important thing I feel needs to be included here is commenting. Not only does it keep you on track with your code so you know what you’re doing, but it makes that code a lot clearer to others — AND yourself! — down the track when you revisit. As you will.
So, here’s what it looks like now:
Public Function dateConv() As String
Dim sfDay As String, sfMon As String, sfBit As String
Dim oSheet As Worksheet, i As Integer
Set oSheet = Worksheets("ItemsPO")
With oSheet
For i = 5 To 540
sfBit = Trim(.Range("B" & Trim(Str(i))).Value)
' traps for an empty record
If Len(sfBit) > 0 Then
' takes all characters before the first /
sfMon = Left(sfBit, InStr(1, sfBit, "/") - 1)
' strip off everything before the second /
sfBit = Mid(sfBit, Len(sfMon) + 2)
sfDay = Left(sfBit, InStr(1, sfBit, "/") - 1)
sfBit = Mid(sfBit, Len(sfDay) + 2)
' add a 0 to single day and month numbers, so 01
' instead of 1
sfDay = IIf(Len(sfDay) = 1, "0" & sfDay, sfDay)
sfMon = IIf(Len(sfMon) = 1, "0" & sfMon, sfMon)
sfBit = "20" & sfBit
sfBit = sfDay & "/" & sfMon & "/" & sfBit
' save results to the E column
.Range("E" & Trim(Str(i))).Value = sfBit
End If
Next i
End With
Set oSheet = Nothing
End Function
Now, to explain what is happening, here. The bit about the spreadsheet being an object allows me to use properties of that object, one of which is “Range”. One of the properties of “Range” is “Value”. You use dots — ‘.’ — to link the property to the object, and attribute to the property, thus:
Object.Property.[Property].Attribute oSheet.Range([range-Length).Value
In Excel, a range can be as small as one cell. For range-Length, it will typically be the coordinates of the cell, naming the column first — which is ‘B’, and then the row number second. This is expressed as a string, as in: ‘B7’. Since we are stepping through the rows, the number will change. This stepping is done with the ‘For – Next’ code, incrementing ‘i’ from 5 to 540 (which represent the rows being processed). ‘i’ is an integer, so it will need to be converted to a string character in order to add — actually, ‘concatenate’, or put next to each other – the column character and the row number. Which is what ‘Str’ does… however, for some silly reason the number (say, 5) becomes “5 ” (with a space next to it), so:
sfBit = .Range("B" & (Str(i)).Value
will give you:
sfBit = .Range("B5 ")
which will generate an error, since that space is seen as an invalid character. Hence the Trim() function there, and for the value for the active cell that is to be stored in sfBit.
‘sfBit’ is my variable that pretty much changes in value as the process moves forward. After I get the Month number, I strip that bit off, then get the Day number, strip that bit off, then with the remaining number tack on a ’20’ in front of it, then concatenate the whole thing together with:
sfBit = sfDay & "/" & sfMon & "/" & sfBit
I write that value to the ‘E’ column in the spreadsheet so one can compare what’s been done and that the conversion has actually taken place. I actually had to highlight that column and set data type to Text… TWICE, because Microsoft flipped the formatting back to mm/dd/yyyy on strings — this is text data, remember! — that it figured were dates. Not all of the entries, mind you, just the ones it thought could be converted. And setting the data type to Custom with format dd/mm/yyyy did exactly NOTHING.
To run this code, click on the VCR ‘Play’ button… the whole thing happens in a split-second.
So, why do it with code instead of just going in and changing each one manually? Did I really save that much time doing it this way? To answer this, one has to like problem-solving and letting the computer do what it does best: repetitive tasks. Doing this process 536 times would have been mind numbing, and this way, if you collect more data from a site, the conversion process will be trivial. And you now *own* the process, as opposed to doing things someone else’s way. When you write code, the process becomes yours, computing becomes yours… it all belongs to you, now.
That is the beauty of freedom software… you’ve taken ownership of your tool.