Now() As Record Id

  • I seek advice on using the value of NOW() as a record ID in an address book program.

    Question #1: Do Excel developers often use a record ID?

    Question #2: What record ID schemes are fequently employed besides date/time?

    I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.

    I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered.

    I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.

    Dim n As Double
     n = Now()
     sn = Str(n)
     p = InStr(sn, ".")
     first = Left(sn, (p - 1))
     l = Len(sn)
     d = l - p
     S = Mid(sn, (p + 1), d)
     f = Format(n, "Short Date") & " " & Format(n, "Long Time") & " " & Format(n, "m/d/yyyy h:mm")
     MsgBox sn & " " & first & " " & S & " " & f

    I feel I can use a NOW() datetime stamp not only as a source of an arbitrary record ID, but also for a field indicating date/time last viewed and a field showing when last modified.

    I shall value any tips or caveats regarding schemes for such a record ID in Excel.

    As a note of interest, I used an old version of Act! Contact Management with success for several years. When I took this new job, six months ago, I found that it was necessary to update to the latest version of Act! which by now was SQL based, rather than the Codebase xbase back end that Act! had used for so many years. I discovered that the new release was much slower, and also harder to use, than the old version I had been used to. A few weeks of study in Access produced something which was much faster and easier for me to work with, and also far easier to customize with fields and features unique to my company's needs. I am certain that if I develop the same Contact management application in Excel, that it will be easier still, once I master Excel and VBA.

    I have exported my Access Contact data to an Excel spreadsheet as a starting point for my project.


  • Re: Now() As Record Id

    I had a similar piece in a project of mine. I keep looking at your code and getting a little lost (which might be because I'm in the same boat as you, learning VBA as I make mistakes with it).

    Do you need the timestamp to go into a cell at the end of the record you've added? If so, the code can be much simpler:

    Cells(y, x) = Now

    (where Cells(y, x) is the cell you want the Timestamp to land. It could also be ActiveCell or an ActiveCell.Offset(y, x))

    Does that fit your needs? It worked for me, so I'm tossing it out there.

    Many thanks, :music:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!