VBA & Macro Problems

How to Convert Decimal Degrees to DMS with Decimal Seconds in VBA

Muhammad Talha Oct 12, 2026 869 views

Question details

The user needs a VBA macro function that converts decimal degrees into a Degrees, Minutes, and Seconds (DMS) format without truncating the fractional seconds.

Convert Decimal Degrees to DMS with Decimal Seconds in VBA

Product: Spreadsheets
Device & OS: not provided
Scenario: Creating custom VBA functions for processing geographical or scientific data requiring high-precision conversions.
Observed behavior: Standard VBA approaches using the 'Int' function truncate the calculated seconds to whole numbers, losing the necessary decimal precision.

Before you start

Ensure that your spreadsheet application has macros enabled and that the Developer tab is visible so you can access the VBA Editor.

Solution 1Recommended

Use a Custom VBA Function with Double Precision

This recommended VBA script calculates the seconds using a Double data type instead of Integer or Long, and formats the output to retain your desired number of decimal places.

By replacing the Int function with Fix and using a Double for the seconds variable, you prevent VBA from rounding down or dropping the fractional digits.

Open the VBA Editor

Press ALT + F11 on your keyboard to launch the Visual Basic for Applications (VBA) Editor.

Insert a New Module

Click 'Insert' from the top menu, then select 'Module' to create a blank script window.

Paste the Code

Copy and paste the following function: Function ConvertDegree(ByVal value As Double) As String Dim d As Long, m As Long, s As Double d = Fix(value) m = Fix((value - d) * 60) s = (value - d - m / 60) * 3600 ConvertDegree = d & "° " & m & "' " & Format(s, "0.00") & "''" End Function

Use the Function in Your Worksheet

Return to your spreadsheet. In any cell, type =ConvertDegree(A1) (assuming your decimal degree value is in cell A1) and press Enter.

Use a Custom VBA Function with Double Precision

Precision Adjustment: You can adjust the Format string '0.00' to '0.000' in the code if you need three decimal places for the seconds.

Solution 2

Alternative: Use Built-in Worksheet Formulas

If you prefer not to use macros, you can extract the degrees, minutes, and precision seconds using standard mathematical cell formulas.

Run Macros Smoothly in WPS Spreadsheet

Easily Run Custom VBA Macros with WPS Office

WPS Spreadsheet features robust built-in support for VBA macros, allowing you to use custom functions for data conversion like DMS formats smoothly and efficiently.

1. Download and Install: Install WPS Office Free and open your workbook in WPS Spreadsheet.
2. Access Developer Tools: Navigate to the Developer tab on the ribbon and click on 'Visual Basic'.
3. Apply Custom Macros: Insert a new Module, paste your DMS conversion VBA code, and use it exactly as you would in Excel.

Fully compatible with Microsoft Excel (.xlsm) formats and VBA syntaxEasily write, edit, and debug scripts inside the integrated VBA EditorFree and lightweight alternative to standard office suites

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA function return a #NAME? error in the cell?

A #NAME? error typically occurs if macros are disabled in your spreadsheet settings, or if the function name you typed in the cell (e.g., =Convert_Degree) does not perfectly match the function name declared in your VBA script.

How can I change the decimal precision of the seconds in the VBA output?

In the provided VBA code, locate the Format function parameter Format(s, "0.00"). Change the "0.00" to "0.000" for three decimal places, or "0.0" for a single decimal place.

Why does my old macro truncate seconds to whole numbers?

If your script uses the VBA 'Int' function for calculating seconds, or if the seconds variable is declared as an 'Integer' or 'Long' instead of a 'Double', the program will automatically drop any fractional decimal values.

Excel & Data Problems