Convert between lat/long and UTM

[Lucas]

Hi, I'm following the guide posted by LUTFOR RAHMAN SHIMANTO (https://www.exceldemy.com/convert-lat-long-to-utm-excel/) to convert between latitude/longitude and UTMs. Everything works except for the CalculateUTMNorthing function, which returns a VALUE error. Any tips on where I went wrong? Thank you in advance!

 

[Lutfor Rahman Shimanto]

Hi, I'm following the guide posted by LUTFOR RAHMAN SHIMANTO (https://www.exceldemy.com/convert-lat-long-to-utm-excel/) to convert between latitude/longitude and UTMs. Everything works except for the CalculateUTMNorthing function, which returns a VALUE error. Any tips on where I went wrong? Thank you in advance!

Hello Lucas

Thanks for reaching out and sharing your queries. The presented User-defined function for calculating UTM Northing in the article should be compatible with most versions of Excel. If you want more compatibility, consider making a few adjustments: replacing the # symbol (used to denote doubles in some VBA versions) and using the CDbl function.

UPDATED User-defined Function for Calculating UTM Northing:

Function NewCalculateUTMNorthing(latitude As Double, longitude As Double) As Double

    Dim utmZone As Integer
    utmZone = Int((longitude + 180) / 6) + 1

    Dim hemisphere As Integer
    If latitude < 0 Then
        hemisphere = -1
    Else
        hemisphere = 1
    End If

    Dim CentralMeridian As Double
    CentralMeridian = (utmZone - 1) * 6 - 180 + 3

    Dim ScaleFactor As Double
    ScaleFactor = 0.9996

    Dim EquatorialRadius As Double
    EquatorialRadius = CDbl(6378137)

    Dim EccentricitySquared As Double
    EccentricitySquared = CDbl(0.00669438)

    Dim N As Double
    N = EquatorialRadius / Sqr(1 - EccentricitySquared * Sin(latitude * 3.14159265358979 / 180) ^ 2)

    Dim T As Double
    T = Tan(latitude * 3.14159265358979 / 180) ^ 2

    Dim C As Double
    C = EccentricitySquared * Cos(latitude * 3.14159265358979 / 180) ^ 2

    Dim A As Double
    A = Cos(latitude * 3.14159265358979 / 180) * (longitude - CentralMeridian) * 3.14159265358979 / 180

    Dim M As Double
    M = EquatorialRadius * ((1 - EccentricitySquared / 4 - 3 * EccentricitySquared ^ 2 / 64 - 5 * EccentricitySquared ^ 3 / 256) * latitude * 3.14159265358979 / 180 - (3 * EccentricitySquared / 8 + 3 * EccentricitySquared ^ 2 / 32 + 45 * EccentricitySquared ^ 3 / 1024) * Sin(2 * latitude * 3.14159265358979 / 180) + (15 * EccentricitySquared ^ 2 / 256 + 45 * EccentricitySquared ^ 3 / 1024) * Sin(4 * latitude * 3.14159265358979 / 180) - (35 * EccentricitySquared ^ 3 / 3072) * Sin(6 * latitude * 3.14159265358979 / 180))

    Dim UTMScaleFactor As Double
    UTMScaleFactor = 0.9996

    Dim UTMFalseNorthing As Double
    If hemisphere = 1 Then
        UTMFalseNorthing = 0
    Else
        UTMFalseNorthing = 10000000
    End If

    Dim UTMNorthing As Double
    UTMNorthing = UTMScaleFactor * (M + N * Tan(latitude * 3.14159265358979 / 180) * (A ^ 2 / 2 + (5 - T + 9 * C + 4 * C ^ 2) * A ^ 4 / 24 + (61 - 58 * T + T ^ 2 + 600 * C - 330 * EccentricitySquared) * A ^ 6 / 720)) + UTMFalseNorthing

    NewCalculateUTMNorthing = CDbl(UTMNorthing)

End Function

OUTPUT OVERVIEW:

​

I am letting you know that the Excel VBA User-defined function for calculating UTM Northing is working perfectly at our end. It has yet to be mentioned that we are using Microsoft 365.

​

So, use the Updated user-defined function and let us know mentioning which Excel Version you are currently using.

Regards
Lutfor Rahman Shimanto
ExcelDemy

 

[Lucas]

Thank you for the reply! Unfortunately the NewCalculateUTMNorthing function is still producing the same VALUE error message.

I'm using Microsoft Excel 2016 (16.0.5422.1000) MSO (16.0.5422.1000) 32-bit. Is this version too outdated to run this successfully?

The Easting and Zone functions work perfectly.