Error in custom function calculation lat long to UTM and UTM to Long lat.

[spring1268]

Hello, as shown in the image below, the custom functions in the three attached XLSM files are not being recognized. I am using Office 2019. Could you please check and update these files? Thank you very much. Could you also check if this is a VBA issue or something else? Please help me upgrade and update the files so they work correctly. I am using the Chinese version of Office 2019. Thank you again.

 

[shamimarita]

Hello @spring1268,

Thanks for the details! Our original tutorial and sample files were created and tested on Excel 365 (Standard). Your screenshot shows Office 2019, and “custom function not recognized” in .xlsm files almost always comes down to macro loading and module placement (not a language pack issue).
Please try the checklist below; this resolves 95% of UDF problems across 2013/2016/2019/365.

1. Unblock & Enable Macros

1. Close Excel. Right-click the downloaded .xlsm file → Properties → if you see Unblock, check it → OK.
2. Reopen Excel → File ▸ Options ▸ Trust Center ▸ Trust Center Settings
   • Macro Settings: choose Disable all macros with notification (so you can enable on open).
   • Trusted Locations: optionally add the folder where the file lives.
3. Open the workbook and click Enable Content (security banner).

2. Make Sure the VBA Code Sits in a Standard Module

• Press Alt+F11 → Project pane → expand your workbook.
• The UDFs (e.g., LatLonToUTM, UTMToLatLon) must be in Module1/ModuleX created via Insert ▸ Module.
  If the code is inside Sheet1 or ThisWorkbook, Excel won’t expose the function to cells.

3. Check the Exact Function Name & Call Syntax

• In the VB Editor, confirm the function name, spelling, and capitalization, then in a worksheet, enter it like:
• =LatLonToUTM($B2,$C2) // example: latitude in B2, longitude in C2
• In Chinese-localized Excel, the argument separator is usually “;”. If, gives an error, try:
• =LatLonToUTM($B2;$C2)
• VBA UDF names are not localized, so you must use the English function name exactly as defined in code.

4. 64-bit Office? Add PtrSafe If There are API Declarations

If the module declares Windows API functions (some older samples do), change e.g.:

Declare Function ...

to

Declare PtrSafe Function ...

(or remove the API calls if they’re not actually used in the math).

5. Decimal & List Separators

• File ▸ Options ▸ Advanced → “Use system separators.” If your data uses dot decimals but Windows uses commas, either toggle that setting or ensure your inputs are consistent. (This affects worksheet inputs, not the VBA math, but it can look like a function failure.)