Variable = 0 Causing Code failure

[sassriverrat]

Hello-
I have two pieces of code, one recently and graciously written by a user. However, I seem to have an issue with it- I have a macro that creates, formats, and populates sheets. If I run the "sheet creator" macro with these two pieces of codes, then the militarytotime debugs on the red line below. If I disable the militarytotime function (just using apostrophes) and then re-enable it after the sheet is created, it works perfectly. I've also noted that this issue only lies if the variable is 0 (i.e. there was nothing in the reference cell (=militarytotime(A1)) when the sheet was created. If the reference cell was not blank, then the macro runs smoothly and all is well.
But yes, reference cell is blank and the macro bugs out before it can finish creating the sheet.
Ideas?

Function Miltime(T1 As Integer)' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50
' Output, time as serial time e.g, 0.5 for noon.


Dim TT1 As Double
TT1 = Int(T1 / 100) + (((T1 / 100) - Int(T1 / 100)) / 0.6) '23.50
TT1 = TT1 / 24
Miltime = TT1


End Function


Function MilitarytoTime(Miltime As String) As Date
MilitarytoTime = Format(Replace(Miltime, ":", ""), "00:00")
End Function

 

[Norie]

Perhaps.

Function MilitarytoTime(Miltime As String) As Date
    If MilTime<>"" Then 
        MilitarytoTime = Format(Replace(Miltime, ":", ""), "00:00")
    End If
End Function

 

[TotallyConfused]

Perhaps.

Function MilitarytoTime(Miltime As String) As Date
    If MilTime<>"" Then 
        MilitarytoTime = Format(Replace(Miltime, ":", ""), "00:00")
    End If
End Function

Norrie:
I noticed in Sassriverrat's posting #1 , that it was mentioned a problem occurs if cell 'A1' is null (blank) or a '0'. I'm certainly no expert, but is it possible their sheet is set to not display zeros, which could mean there is a zero in cell 'A1', but it would appear as blank. I would think a zero and null are two different things. If a zero was there, wouldn't your formula, as it's written, allow the zero value to be passed on, which would still cause a problem?
If that were the case, would something like the following work?

[COLOR=black]     
Function MilitarytoTime(Miltime As String) As Date
     If(Or(MilTime<>{"",0} Then [/COLOR]
  [COLOR=black]          MilitarytoTime = Format(Replace(Miltime, ":", ""), "00:00")[/COLOR]
[COLOR=black]     End If[/COLOR]
[COLOR=black]End Function[/COLOR]

As a secondary thought, would it be better to place this 'IF statement' in the original macro, and decide there whether or not to even call these functions? As I said earlier, I'm certainly not an expert in Excel and VBA, so I may be totally wrong, which wouldn't be the first time that has happened.


TotallyConfused

 

[Norie]

Good point, but I think the problem might be that the cell is empty especially since the OP mentions it's occurring when a new sheet is being created.

I suppose we'll have to wait for feedback.

 

[sassriverrat]

So to answer precisely, yes, it happens when the cell is empty (i.e. hasn't been selected and filled in yet) which is when the sheet is being created.

 

[Norie]

Did the alteration to the code I suggested work?

 

[sassriverrat]

Oh yes sir it did. Sorry I thought I had replied the other day. Fingers crossed I’m releasing the workbook today to it’s first set of users!