VBA & Macro Problems

How to Fix the VBA Convert_Degree Function and Degree Symbol Error in Excel

Guest Writer Sep 28, 2026 869 views

Question details

The user needs to correct a custom VBA function that incorrectly calculates decimal degrees into minutes and seconds, and fails to display the degree symbol properly.

How to Fix the VBA Convert_Degree Function and Degree Symbol Error

Product: Excel VBA
Device & OS: not provided
Scenario: Converting a decimal degree value into a properly formatted string of Degrees, Minutes, and Seconds using a custom VBA macro.
Observed behavior: The function calculates 60 seconds instead of rolling over to the next minute (e.g., 19 degrees, 52 minutes, 60 seconds instead of 19 degrees, 53 minutes), and the degree symbol generated by Chr(176) displays as an incorrect character.

Before you start

Ensure the Developer tab is enabled in your spreadsheet ribbon and that your macro security settings are configured to allow VBA execution.

Solution 1Recommended

Fix the VBA Calculation Logic and Use ChrW for Unicode

Modify the math to round total seconds before extracting minutes, and replace the ANSI character code with the Unicode equivalent for the degree symbol.

When converting decimal degrees, calculating the minutes first and then rounding the seconds can lead to a 60-second rollover bug. Rounding the total seconds at the very beginning ensures accurate division using the Mod operator. Additionally, standard ANSI Chr(176) can render improperly depending on system locale; ChrW(176) uses Unicode and guarantees the correct degree symbol.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications editor. Locate the module containing your Convert_Degree function in the Project Explorer.

Update the rounding logic

Replace your current math operations with the following logic: first calculate total rounded seconds using 'Seconds = Round(3600 * Decimal_Deg, 0)', then extract degrees using 'Degrees = Seconds \ 3600', extract minutes using 'Minutes = (Seconds Mod 3600) \ 60', and finally get the remaining seconds with 'Seconds = Seconds Mod 60'.

Replace Chr with ChrW

Update the final output string concatenation line. Change 'Chr(176)' to 'ChrW(176)' so the output statement reads: Convert_Degree = Degrees & ChrW(176) & " " & Minutes & Chr(39) & " " & Seconds & Chr(34).

Fix the VBA Calculation Logic and Use ChrW for Unicode

Code Updated: Your function will now correctly rollover to the next minute instead of showing 60 seconds, and the degree symbol will display perfectly across all system languages.

Solution 2

Use a Native Spreadsheet Formula Instead of VBA

Bypass VBA entirely by using the built-in TEXT function to convert decimal degrees to a Degrees, Minutes, Seconds format.

Efficient VBA handling with WPS Office

Easily Run and Debug VBA Macros in WPS Office

WPS Spreadsheet fully supports advanced VBA macros and native Excel formulas, allowing you to run, edit, and debug custom functions like Convert_Degree effortlessly. It provides a familiar interface with exceptional format compatibility.

1. Install WPS Office: Download and install the free version of WPS Office from the official website.
2. Enable the Developer Tab: Open WPS Spreadsheet, go to Options, and enable the Developer tab to access macro tools.
3. Insert your VBA Code: Click on 'VBA Editor', insert a new module, and paste your corrected Convert_Degree function.
4. Run your Macro: Save your workbook as a macro-enabled format and use your custom function directly in any cell.

Full support for VBA macros and advanced spreadsheet functionsSeamless compatibility with Microsoft Excel (.xls, .xlsx, .xlsm) formatsLightweight architecture ensuring fast loading and smooth calculationsFamiliar ribbon interface for a zero-learning-curve transition

microsoft office alternative - wps office

Frequently Asked Questions

Why does Chr(176) show the wrong symbol in VBA?

The Chr() function relies on the local system's default ANSI character set, which can vary by region and language settings. If the local system encoding does not map 176 to the degree symbol, an incorrect character appears. Using ChrW(176) forces the use of Unicode, which guarantees the correct degree symbol regardless of regional settings.

Why does my conversion result in 60 seconds instead of adding a minute?

This happens due to rounding errors during the extraction sequence. If you extract the minutes based on the unrounded decimal and then round the remaining seconds up, a value like 59.6 seconds becomes 60 seconds without updating the minute count. Rounding the total seconds before any division ensures all subsequent minute and degree calculations are completely accurate.

Can I format degrees, minutes, and seconds without using a macro?

Yes. Because spreadsheets calculate time in fractions of a 24-hour day, you can divide your decimal degree by 24 and format it using the TEXT function. The formula =TEXT(A2/24,"[h]° mm' ss")&CHAR(34) works perfectly as a non-VBA alternative.

Excel & Data Problems