• PC NEWS: update is out! More info here. Report bugs here
  • CONSOLE 1.4 NEWS: The current plan is to have Terraria: Journey's End out for Xbox and PlayStation in Q3 2021 (Jul-Sep). Switch will follow shortly afterwards. More info here. Stay up to date with console news here
  • MOBILE NEWS: Stay up to date with the latest mobile news here. Report mobile bugs here

Tool Excel VBA function to format money amounts as platinum/gold/silver/copper


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.

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:
Top Bottom