# ToolExcel VBA function to format money amounts as platinum/gold/silver/copper

#### Bethany

##### Steampunker
Note: This post is intended for Microsoft Excel users who feel confident using Excel's Developer tools, specifically the Visual Basic environment. The code is simple and straightforward, and I encourage users to read and understand it before use so you'll know there's no funny business. Running unknown VB code in Microsoft Office can harm your computer or the data on it.

Here's a Visual Basic for Applications function that converts an amount in silver "dollars", such as 20418.50, into a string representing the amount in Terraria platinum, gold, silver, and copper pieces, such as "2p 4g 18s 50c". Feel free to use this in any spreadsheet you make that calculates using amounts in whole and fractional silver pieces but which you want displayed in game terms.

I wrote this while figuring out how much a fishing trip to the ocean earned. Seeing amounts as if they were dollars seemed not quite right, so I created extra columns for formatted money amounts and hid the "dollar" columns. I'd like to be able to use this as an Excel cell format but don't know whether that's possible.

Code:
``````Option Explicit

Public Function DollarsToPGSC(Dollars As Single) As String

Dim plat As Integer, gold As Integer, silv As Integer, copr As Integer

DollarsToPGSC = ""
If (Dollars >= 10000) Then
plat = Int(Dollars / 10000)
DollarsToPGSC = CStr(plat) & "p "
Dollars = Dollars - 10000 * plat
End If
If (Dollars >= 100) Then
gold = Int(Dollars / 100)
DollarsToPGSC = DollarsToPGSC & CStr(gold) & "g "
Dollars = Dollars - 100 * gold
End If
If (Dollars >= 1) Then
silv = Int(Dollars)
DollarsToPGSC = DollarsToPGSC & CStr(silv) & "s "
Dollars = Dollars - silv
End If
If (Dollars > 0) Then
copr = Int(100 * Dollars)
DollarsToPGSC = DollarsToPGSC & CStr(copr) & "c"
End If
DollarsToPGSC = RTrim\$(DollarsToPGSC)

End Function``````

Using Excel's Visual Basic window you can put this code in a module for reuse, or you might prefer to add it directly in a workbook or sheet. To use it, suppose cells A3:A12 contain amounts in silver "dollars" and you want column B to display those values in platinum/gold/silver/copper notation. Into Cell B3 place the formula =DollarsToPGSC(A3) , then copy that and paste it into cells B4 through B12; B4 will contain =DollarsToPGSC(A4) , B5 will contain =DollarsToPGSC(A5) , and so on. If you like, hide column A to see just the "pgsc" values in column B.

Last edited: