using now() function not in a cell
 

Hi there,

I'm trying to use the Now() function without accessing a cell in the
spreadsheet. Trying to assign the datetime to a string for a log file,
but I don't want to have to access the worksheet when doing it. Any
ideas?

Cheers

using now() function not in a cell
 

I'm not quite clear on what question is, but you can get the current time in
a formatted string with code like

Dim S As String
S = Format(Now, "dd-mmm-yyyy hh:mm:ss")
Debug.Print S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Gordo" wrote in message
ups.com...
Hi there,

I'm trying to use the Now() function without accessing a cell in the
spreadsheet. Trying to assign the datetime to a string for a log file,
but I don't want to have to access the worksheet when doing it. Any
ideas?

Cheers

using now() function not in a cell
 

Thanks for your response Chip, I get a compile error when I try to run
that though. the now is highlighted and the error message is 'Expected
Function or variable'
I've tried it with the function brackets as well with the same result.

Best regards,
Gord

Chip Pearson wrote:
I'm not quite clear on what question is, but you can get the current time in
a formatted string with code like

Dim S As String
S = Format(Now, "dd-mmm-yyyy hh:mm:ss")
Debug.Print S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Gordo" wrote in message
ups.com...
Hi there,

I'm trying to use the Now() function without accessing a cell in the
spreadsheet. Trying to assign the datetime to a string for a log file,
but I don't want to have to access the worksheet when doing it. Any
ideas?

Cheers

using now() function not in a cell
 

The code works fine for me. Did you use the code exactly as shown? Just to
be sure, did you put the code within a Sub or Function procedure?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Gordo" wrote in message
ups.com...
Thanks for your response Chip, I get a compile error when I try to run
that though. the now is highlighted and the error message is 'Expected
Function or variable'
I've tried it with the function brackets as well with the same result.

Best regards,
Gord

Chip Pearson wrote:
I'm not quite clear on what question is, but you can get the current time
in
a formatted string with code like

Dim S As String
S = Format(Now, "dd-mmm-yyyy hh:mm:ss")
Debug.Print S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Gordo" wrote in message
ups.com...
Hi there,

I'm trying to use the Now() function without accessing a cell in the
spreadsheet. Trying to assign the datetime to a string for a log file,
but I don't want to have to access the worksheet when doing it. Any
ideas?

Cheers

using now() function not in a cell
 

Yes, used code exactly as shown.

tried first in a Sub and then a Function as follows:

Function Mygettime() As String
Dim S As String
S = Format(now, "dd-mmm-yyyy hh:mm:ss")
Debug.Print S

End Function

I get the compile error when I try and run it.

I'm using Excel 2003, and Microsoft Visual Basic 6.3, if that makes any
difference..

Best regards,

Gord




Chip Pearson wrote:
The code works fine for me. Did you use the code exactly as shown? Just to
be sure, did you put the code within a Sub or Function procedure?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Gordo" wrote in message
ups.com...
Thanks for your response Chip, I get a compile error when I try to run
that though. the now is highlighted and the error message is 'Expected
Function or variable'
I've tried it with the function brackets as well with the same result.

Best regards,
Gord

Chip Pearson wrote:
I'm not quite clear on what question is, but you can get the current time
in
a formatted string with code like

Dim S As String
S = Format(Now, "dd-mmm-yyyy hh:mm:ss")
Debug.Print S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Gordo" wrote in message
ups.com...
Hi there,

I'm trying to use the Now() function without accessing a cell in the
spreadsheet. Trying to assign the datetime to a string for a log file,
but I don't want to have to access the worksheet when doing it. Any
ideas?

Cheers

using now() function not in a cell
 

I can't get your code to raise an compiler error. What is the exact wording
of the compiler error?

Regardless, your function is incomplete. There is no code within it to
return the value. You need to add the line

Mygettime = S

This sets the return value of MyGetTime to the contents of the string S.

I don't know would cause a compiler error unless your VBA References are
messed up. In VBA, go to the Tools menu and choose References. If you see
any that are marked "MISSING" then that is the likely cause of your
problems. If the missing reference is one you do not need, simply uncheck it
in the list. If it is a required reference, you'll need to take some steps
to restore the references.

This first thing to do is close Excel and any other programs that may be
running. Then go to the Windows Start menu, choose Run, and enter

"Excel.exe" /unregservier

Then repeat this with

"Excel.exe" /regserver

You may need to use the complete file path to Excel rather than just
"Excel.exe". Something like

"C:\Program Files\Microsoft Office\Office\Excel.exe" /unregserver

Of course, you exact path name may vary. If this doesn't work, choose
"Detect And Repair" from the Help menu, which will cause Excel to
reconfigure itself (it mostly does the same thing as the /regserver command
above does).

See also www.cpearson.com/Excel/StartupErrors.htm for additional diagnostic
procedures.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Gordo" wrote in message
oups.com...
Yes, used code exactly as shown.

tried first in a Sub and then a Function as follows:

Function Mygettime() As String
Dim S As String
S = Format(now, "dd-mmm-yyyy hh:mm:ss")
Debug.Print S

End Function

I get the compile error when I try and run it.

I'm using Excel 2003, and Microsoft Visual Basic 6.3, if that makes any
difference..

Best regards,

Gord




Chip Pearson wrote:
The code works fine for me. Did you use the code exactly as shown? Just
to
be sure, did you put the code within a Sub or Function procedure?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Gordo" wrote in message
ups.com...
Thanks for your response Chip, I get a compile error when I try to run
that though. the now is highlighted and the error message is 'Expected
Function or variable'
I've tried it with the function brackets as well with the same result.

Best regards,
Gord

Chip Pearson wrote:
I'm not quite clear on what question is, but you can get the current
time
in
a formatted string with code like

Dim S As String
S = Format(Now, "dd-mmm-yyyy hh:mm:ss")
Debug.Print S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Gordo" wrote in message
ups.com...
Hi there,

I'm trying to use the Now() function without accessing a cell in the
spreadsheet. Trying to assign the datetime to a string for a log
file,
but I don't want to have to access the worksheet when doing it. Any
ideas?

Cheers