Using 'Now()' as a numeric string

**Phil_V** · 10-01-2008, 08:49 AM

I am trying to create what should be a unique string.
I figured the easiest way to do this would be to use the value given by Now().
However I can't find out how to actually get the result of Now() as a number.
If on a spreadsheet I put in a cell: =Now() and then format that cell as 'General' it gives me the number I am trying to get programatically.

I tried:

Code:

MsgBox "My PreText " & Now()

but this just gives the date formatted as text according to the local formatting. How can I get it to present a msgbox such as:

My PreText: 39722.5299725694

As a slight aside, is there a limit to the number of characters in a worksheet name?

Thanks

**Andy Pope** · 10-01-2008, 08:53 AM

Code:

MsgBox "My PreText " & cdbl(Now())

31 characters

**Phil_V** · 10-01-2008, 09:01 AM

Excellent on both accounts

Thanks Andy

**Phil_V** · 10-01-2008, 09:17 AM

hmm... it seems if this is called in quick succesion the value of Now() hasn't changed, so won't give me unique strings.

My next thought was to add in an 'Rnd' seeded with Now(), so that even if Now() was still the same, the string would be different.
The problem I then had was that the text doesn't always end up numeric due to Excel seeming to convert it to scientific notation.

For example;

Code:

Dim index As Long
With ActiveSheet
    For index = 1 To 10000
        '.Cells(index, 1).value = "Ws" & Rnd(CSng(Now()))
        Debug.Print "Ws" & Rnd(CSng(Now()))
    Next
End With

Some results:

Ws0.9929597
Ws0.4109336
Ws0.2277516
Ws2.578497E-03

I'm trying to generate unique Worksheet names; perhaps there is a better way someone can suggest? (I already know I am going to have to remove all the symbols from this text before I could use it as a ws name)
I thought that using the current time to seed the rnd would be a good method for this, but I want to try and get around the scientific notation thats being applied now (I'd be happy if it just gave me the number!)

**Andy Pope** · 10-01-2008, 09:26 AM

Is that unique within the workbook or truely unique?

**Richard Schollar** · 10-01-2008, 09:26 AM

Why are you trying to generate unique worksheet names? If the sheets all exist in the same workbook, couldn't you use some form of incremental counter in the name based on the total number of sheets? eg ws001, ws002, ws003 etc

Richard

**Phil_V** · 10-01-2008, 10:10 AM

The situation is that I will have many copies of workbook-'A', which has for instance worksheets called:

"Sample"
"Results"
"Test"

I also have another workbook that I am using as a 'backup' area.
When one of 'workbook-'A'' is saved, it will also copy it's worksheets into the backup workbook.
However as there is many workbook'A's the worksheets cannot keep there original name, (as there will be a "Sample" from Bob's workbook'A', and also a "Sample" from Bill's workbook'A')

My plan was to have an index sheet in the backup workbook containing columns of 'Original Workbook', 'Original WS Name', 'New WS Name', and it was the 'New WS Name' that I was trying to generate.

However.... in the typing of this response I've just thought, I guess I could actually set the worksheet name to be a combination of the workbook name, the user, and the worksheet name, and forget all this unique string rubbish!

So "Sample" from Bob would be renamed to be "WorkbookA-Bob-Sample". With the worksheet name able to be relatively long this would work, but could cause issues if there are 2 bob's etc...

I can take this route, or go a route to use unique strings if we can find a solution. (I'd be interested to find out how to create a unique string based on Now() either way

)

Edit Using the user is not going to work, as by the time the username and the original worksheet name have been concatenated it is quite possible that the string will be too long for a worksheet name. For this reason I'd prefer just to generate a unique string 20 or so characters long.
I can't use the index of the Worksheet in the file as it's name, because the worksheets can be deleted from the backup, (if they are deleted from the original workbooks), as well as added.

Eg,
Peter Adds 3 worksheets:
ws01
ws02
ws03
Paul adds 1 worksheet:
ws04
Peter deletes his 3rd worksheet, leaving:
ws01
ws02
ws04
Paul adds another worksheet. If I use the worksheet index appended to 'ws' then it's going to try and create another ws04.

Thanks!

**Richard Schollar** · 10-01-2008, 10:15 AM

I'd be tempted to use something like this:

Code:

Randomize
'start your loop and then
UniqueID = "WS" & CDbl(Now) & Rnd*10000000

Richard

**Andy Pope** · 10-01-2008, 10:17 AM

When not use the row number from the Index sheet?

To get the NOW() approach to return unique values you would need to pause for a second between each usage as the function only returns details to the second.

**Andy Pope** · 10-02-2008, 10:23 AM

How about using the computer time in milliseconds.

Code:

Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub x()

    Dim strTemp As String
    Dim lngLoop As Long
    
    strTemp = CDbl(Now) & vbLf
    strTemp = strTemp & timeGetTime & vbLf
    
    Sleep 5
    
    strTemp = strTemp & CDbl(Now) & vbLf
    strTemp = strTemp & timeGetTime & vbLf
    
    MsgBox strTemp
    
End Sub

**Phil_V** · 10-02-2008, 10:49 AM

That would have been a nice solution I think, can't see my calling the routine faster than each millisecond. Although isn't there still some issue of it not actually incrementing each millisecond. I can't remember where but I seem to remember reading something that although it's in millisecond divisions, it doesn't actually update every millisecond?
Of course I could be completely wrong about that!

For now I've gone with this method which seems to be fairly good, and shouldn't produce duplicates unless I am very very unlucky!

Code:

new_ws_name = Left(Replace("Ws" & CDbl(Now) & (Rnd * 10000000) & (Rnd * 10000000), ".", vbNullString), 31)

The left 31 characters of "Ws" the 'Now' number, and 2 RND numbers, with '.' removed.

**Andy Pope** · 10-02-2008, 11:23 AM

You can always add a check that the value returned is not the same as the previous value. If it is execute the code again.

**shg** · 10-02-2008, 11:39 AM

If this is for a network application, you could have a text file accessible by all that contains sequential numbers. When the workbook is to be saved, open the text file, get the last number, increment it, and write it to the text file -- perhaps along with username, date and time, and anything else useful -- and then save the workbook as wkbxxxx or whatever.