MsOf10 [RESOLVED] [Excel] Weird result in Excel when formatting to time: mm:ss.00-VBForums
Results 1 to 10 of 10

Thread: [RESOLVED] [Excel] Weird result in Excel when formatting to time: mm:ss.00

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jul 2010
    Posts
    148

    Resolved [RESOLVED] [Excel] Weird result in Excel when formatting to time: mm:ss.00

    I have code that extract results from posted track and field results and produces reports for each athlete on our team. For the time results, I format them so that they all have the format mm:ss.00. This means that results for the 55m dash would look like

    00:07.79

    and results for a race walk may look like

    20:05.00

    It looks fine on the worksheet however when I save the reports as PDF files, there is an occasional ? with a box around it. I am formatting using the following command:
    Range("A1").NumberFormat = "mm:ss.00"

    I have been able to determine a difference between the cells that print properly and the ones that produce the funny character.

    Lets say I have two cells, one with 00:07.40 and one with 00:07.79. When I click on the 1st cell, I see the date equivalent in the entry bar (12:00:08 AM) but when I click on the 2nd, I see the same text in the entry bar (00:07.79). I confirmed that both cells are indeed formated using mm:ss.00 by right clicking on the cells and choosing Format Cells. The second cell is printing with the strange character.

    Thank you.
    Last edited by adrian1906; Feb 21st, 2013 at 01:47 PM.

  2. #2
    Super Moderator Joacim Andersson's Avatar
    Join Date
    Jan 1999
    Location
    Sweden
    Posts
    14,649

    Re: Weird result in Excel when formatting to time: mm:ss.00

    Moved to the Office Development forum.
    Joacim Andersson
    If anyone's answer has helped you, please show your appreciation by rating that answer.
    I'd rather run ScriptBrix...
    Joacim's view on stuff.

    MVP

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,887

    Re: [Excel] Weird result in Excel when formatting to time: mm:ss.00

    regardless of what is displayed in the sheet, what is the value of the two cells?
    maybe one is a formatted date, the other a literal string
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jul 2010
    Posts
    148

    Re: [Excel] Weird result in Excel when formatting to time: mm:ss.00

    I verified that they both have the custom format: mm:ss.00. Is there something else that would govern how the information is displayed and stored?

    Thank you.
    Adrian

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,887

    Re: [Excel] Weird result in Excel when formatting to time: mm:ss.00

    Is there something else that would govern how the information is displayed and stored?
    yes, how it is input, formula, typing or code
    Last edited by westconn1; Feb 22nd, 2013 at 01:01 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Jul 2010
    Posts
    148

    Re: [Excel] Weird result in Excel when formatting to time: mm:ss.00

    The information is entered into the cells two ways. Either by hand or imported directly from a website. I thought that making sure the cells shared the same format was enough. What else needs to be adjusted?

    Thank you.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,887

    Re: [Excel] Weird result in Excel when formatting to time: mm:ss.00

    The information is entered into the cells two ways.
    can you determine if the different entry methods, are the cause of the different print out?

    What else needs to be adjusted?
    i can not tell without seeing a sample of the inputs, check for leading spaces
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Jul 2010
    Posts
    148

    Re: [Excel] Weird result in Excel when formatting to time: mm:ss.00

    I determined the problem.

    My code extracts data from a .csv file with the following format:
    Name,AgeGroup,Event,Mark
    Jessie Parson,11-12,55m Hurdles,9.13
    Jessie Parson,11-12,55m Hurdles,9.49
    Eric Allen,13-14,55m Hurdles,8.4

    It first calls a procedure that adds the necessary 0's and colons to get the "mm:ss.00" format:

    Code:
    '******************************************************
    'Used to convert time given in seconds to the mm:ss.00 format
    Public Function ConvertTimeFunction(myvalue As Variant) As String
        Dim TimeStr As String
        Dim temp() As String
        Dim temp2() As String
        On Error GoTo Err_Clear
        temp = Split(myvalue, ":")
        If UBound(temp) = 2 Then    ' check for too many :
            TimeStr = myvalue
            MsgBox "Problem with the value " & myvalue
        Else
            temp = Split(myvalue, ".")
            If UBound(temp) = 0 Then
                myvalue = myvalue + ".00"
            End If
            temp = Split(myvalue, ":")
            If UBound(temp) = 0 Then
                myvalue = "00:" & myvalue
            ElseIf temp(0) = "" Then
                myvalue = "00:" & myvalue
            ElseIf temp(0) = 0 Then    'case for format 0:ss.00
                TimeStr = "0" & myvalue
            End If
            temp = Split(myvalue, ":")    ' redo
            ConvertTimeFunction = Format(temp(0), "0#") & ":" & Format(temp(1), "0#.00")
        End If
    Err_Clear:
        If Err <> 0 Then
            MsgBox "Problem with the value " & myvalue
            Err.Clear
            Resume Next
        End If
    End Function
    It then writes the value to a cell on the performance sheet, sets the correct cell format, and then produces a report. On the performance sheet, some times are imported from the .csv file and some are manually entered. In the report, the manually entered times print ok but the auto imported values produced a strange character after the times.

    I determine that it had to do with the way I declared my variable in my DataImport() procedure. I had mistakenly thought that the following lines were valid:
    Dim tmpNF, tmpvalue, tmpmeet as string
    Dim startcell, stopcell, NewDatum, Datum_Report, Datum_Data, MyNewRange As Range

    where the tmpvalue is used to hold the "Mark" value extracted from the .csv file and NewDatum.Offset(x,y).value =tmpvalue is used to write the value to a cell. NewDatum.Offset(x,y).NumberFormat = "mm:ss.00" is used to set the format.

    Two things I noticed:
    1.) When declaring variables, they must be independently declared (unlike VB.net)
    2.) The issue was resolved when I first set the .NumberFormat for the cell and then wrote the value

    This one was a tough one to debug because I received no indication that the method of declaration was invalid. I always write with Option Explicit expecting to be told of undeclared variables.

    Thanks for all the help.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,887

    Re: [RESOLVED] [Excel] Weird result in Excel when formatting to time: mm:ss.00

    I always write with Option Explicit expecting to be told of undeclared variables.
    your declarations were valid, just not typed, any not specifically typed would be of type variant (microsofts default), so no warning would be given
    the text assigned to a variant may not be the same type as that coereced into a variable of specific type (or type mismatch if not possible)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Jul 2010
    Posts
    148

    Re: [RESOLVED] [Excel] Weird result in Excel when formatting to time: mm:ss.00

    Earlier I wrote that the culprit was the fact that I didn't explicitly delcare my variables as strings therefore they became variant by default. It turns out that it wasn't the culprit after all (Phew...). The true reason is that a SPACE or Carriage Return or Line Feed is being appened to my extracted data. The computer I was using when I thought I found the answer produced PDF files that was able to 'handle' the end character (either Space,CR, or LF) leading me to conlcude that the problem was solved.

    The issue is the way I created the .csv file: The following line was used:
    Code:
    MyMatch(2) & " " & MyMatch(3) & "," & AGandEvent & "," & ConvertTimeFunction(value))
    By not ending each line with a comma, a space was inadvertantly added to the string when extracted using the split(MyLineOfValues,",") command. Westconn1 mentioned looking for spaces but my technique was flawed. In the .cvs file, I simply placed the curser at the end of one row and used the arrows to move to other rows. Each time, the curser would sit right next to the value indicating no spaces. However, when I highlighted the entire row, the extra space became apparent.

    The fix,

    I fixed the problem by simply adding another comma.
    Code:
    MyMatch(2) & " " & MyMatch(3) & "," & AGandEvent & "," & ConvertTimeFunction(value) & ","
    Thanks
    Last edited by adrian1906; Mar 4th, 2013 at 12:20 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.