VBA Code - Minimize the code/lines

[OilEconomist]

Thanks in advance for any assistance. Is there a way to reduce the number of lines in the following code. Please note what the code does it allows me to enter a value like 4 and then converts it to 04:00 AM EST. I want to make sure it keeps the leading 0 before the 4.

Sub RdcCode()

For i = 6 To 11
    Range("D" & i) = "=TIME(B" & i & ", 0, 0)"
    Range("D" & i).NumberFormat = "hh:mm AM/PM ""EST"""
    Range("D" & i).Formula = Range("D" & i).Value
Next i

End Sub

 

[Peter_SSs]

Is there a way to reduce the number of lines in the following code.

Why? What is wrong with a procedure of 7 lines in total? (Though if keeping that procedure I would add a line as I think it good practice to Dim all variables)

Still, this would be one less line

Sub RdcCode_v2()
  With Range("D6:D11")
    .NumberFormat = "hh:mm AM/PM ""EST"""
    .Value = Evaluate("time(" & .Offset(, -2).Address & ",0,0)")
  End With
End Sub

 

[OilEconomist]

Why? What is wrong with a procedure of 7 lines in total? (Though if keeping that procedure I would add a line as I think it good practice to Dim all variables)

Still, this would be one less line

Sub RdcCode_v2()
  With Range("D6:D11")
    .NumberFormat = "hh:mm AM/PM ""EST"""
    .Value = Evaluate("time(" & .Offset(, -2).Address & ",0,0)")
  End With
End Sub

Thanks @Peter_SSs that works! It is part of a larger macro which I was trying to reduce the lines anywhere possible.

I agree with you about it's a good practice to Dim all variables. It was part of the larger code, but I accidentally left it out.

 

[Peter_SSs]

I agree with you about it's a good practice to Dim all variables. It was part of the larger code, but I accidentally left it out.

OK, fair enough.

I was trying to reduce the lines anywhere possible.

Of course, less lines does not always mean better or more efficient code/processing.

 

[OilEconomist]

OK, fair enough.


Of course, less lines does not always mean better or more efficient code/processing.

Ah. Yes I see and good point because now that I think about it, it could make it harder to understand when trying to find an error or trying to walk through the logic.

 

[vw412]

Ah. Yes I see and good point because now that I think about it, it could make it harder to understand when trying to find an error or trying to walk through the logic.

If you must reduce code to the point it becomes obtuse (hard to remember what it does) be sure to add explanatory comments. Take it from a retired programmer (50 years doing it) you will not remember why you wrote that particular block of code in that way in 6 months.