Modify VBA so variable is inputted in the worksheet before Macro is launched

[Livin404]

Greetings I have a Public Function that works exactly the way I hope it would. The Public Function is immediately below. The second MACRO works with the Public Function. These two along with other numerous Macros created a schedule. The issue I have is I would want to share this with numerous other groups located around the world. The scheduled we get is based on Greenwich Meantime (Zulu), but we convert it to local time based on where we are located. I accounted for this for my current location see JulianFinalResult = Format(ConvertedDate + TimePortion - TimeSerial(5, 0, 0), "d mmm yyyy h:mm:ss AM/PM"). You will see this near the bottom of the first Macro. MACRO was provided from a member in this group.

It is the 5 which is the difference between Zulu and Local. Again my Macro is solid, I just want one tweak if possible. My formats and everything else is perfect



My ultimate goal is to have an option where I can enter a number 5, or 6 or -5 or -7 etc. in a cell on my worksheet say "Outbound" cell R6. This way if I were to share my workbook, I wouldn't have to instruct people to go to developer to manually change the line. It would also work, so I do not need to go and change it every time we move from Standard to Daylight Savings Time and vice versa.

I had some help from a third party source and where we came up with. This would be swapped for the line I mentioned in the first paragraph.

JulianFinalResult = Format(ConvertedDate + TimePortion - TimeSerial(ThisWorkbook.Worksheets("Outbound").Range("R6").Value, 0, 0), "d mmm yyyy h:mm:ss AM/PM")

. Fortunately, there were no errors, but it was not adding or subtracting the number I placed in "R6".

Thank you very much indeed.

Public Function Julian(JulianDateString As String)           

    Dim ConvertedDate       As Date
    Dim TimePortion         As Date
    Dim CalenderDays        As Long
    Dim CalenderYear        As Long
    Dim JulianFinalResult   As String
    Dim JulianDate          As String

    JulianDate = Left(JulianDateString, 4)

    CalenderDays = CLng(Right(JulianDate, 3))

    If Len(JulianDate) < 4 Then

        CalenderYear = 2020     
    Else
        CalenderYear = 2020 + CLng(Left(JulianDate, 1))

    End If

    ConvertedDate = DateSerial(CalenderYear, 1, CalenderDays)                                                

    TimePortion = TimeValue(Left(Right(JulianDateString, 4), 2) & ":" & Right(Right(JulianDateString, 4), 2))   ' Returns a Time

  [B] [/B] JulianFinalResult = Format(ConvertedDate + TimePortion - TimeSerial(5, 0, 0), "d mmm yyyy h:mm:ss AM/PM")      ' Returns a Date/Time string[B] [/B]

    Julian = JulianFinalResult                                                                               
End Function

This is the MACRO that is used with the Public Function

Sub CopyJulianUDFFormulas_Calculate_DeleteJulianFormulas_DeleteOriginalJulianDataColumn()

     With Range("G1:G" & Range("F" & Rows.Count).End(xlUp).Row)

        .Formula = "= julian(F1)"     
        .Value = .Value   

        .NumberFormat = "d mmm yyyy h:mm:ss AM/PM"   '' format cells

    End With

    Range("F:F").EntireColumn.Delete  ' Delete source column F which is no longer needed                                                       

End Sub

 

[Norie]

Where is this code stored?

 

[Livin404]

Where is this code stored?

it is in the Module. Everything works perfect. It is just I want to control the number (difference between Zulu and Local) rather than having dozens of people go into the VBA itself to change a number. I described the number in the original post.

Where is this code stored?

The specific line is

JulianFinalResult = Format(ConvertedDate + TimePortion - TimeSerial(5, 0, 0), "d mmm yyyy h:mm:ss AM/PM")

Which is in the first VBA Code and the 3rd line from the bottom. The proposed tweak to the line is in the first formula provided, but it wasn't adding or subtracting the hour(s) I put in cell "R6".

Thank you,