Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
Copy Excel Function from a Cell Through a Function | Excel Worksheet Functions | |||
I want result of a function in the cell and not the function itsel | Excel Worksheet Functions | |||
Can function in one cell change value or function in another cell? | Excel Worksheet Functions | |||
Custom Function: Detecting the cell the function is used in | Excel Programming |