Keep trailing zeros when pasting a double to an external application-CodePudding

I am working on a VBA program which should update some decimal numbers in text fields of another application. The numbers are contained in the range P6:P18 of the worksheet "FX Rates".

My problem is that any trailing zeros are not kept when pasting to the external application. For example, 3.1640 becomes 3.164, which is an invalid value in my case.

A snip of my current setup (only showing one Dim of 18 Ranges with decimal numbers) is shown in the block below. My expectation was that any trailing zeros would be kept when pasting to the external app.

Sub UpdateExchangeRateCuts()

Dim USDDKK As Range: Set USDDKK = Worksheets("FX Rates").Range("P6")

InitializeExternalApp

PasteXY 9, 47, USDDKK

I use a Public Sub (InitializeExternalApp) which basically inserts USDDKK in a text field of another application. This procedure works fine, but any trailing zeros are not kept. I have tried to use NumberFormat in the following way:

Sub UpdateExchangeRateCuts()

Dim USDDKK As Range: Set USDDKK = Worksheets("FX Rates").Range("P6")

USDDKK.NumberFormat = "0.0000"

InitializeExternalApp

PasteXY 9, 47, USDDKK

This does not work, however. I would highly appreciate any tips on how to keep the trailing zeros. Thanks!

CodePudding user response：

Try this:

Dim USDDKK As String
USDDKK = Format(Worksheets("FX Rates").Range("P6").Value, "0.0000")

InitializeExternalApp

PasteXY 9, 47, USDDKK