• For issues you find with the Switch and Console releases, please follow this link and give as much detail as possible. This is the speediest way to get info to Pipeworks and get a hotfix in the works.
  • 1.4 will bring many changes to the PC version. We strongly advise making plans to back up your worlds and players prior to updating your game. More details here.
  • Begin your search for Journey's End information here and here. Please report bugs and issues for Journey's End here.
  • For issues you find with the Mobile 1.3 update, please follow this link and give as much detail as possible. This is the speediest way to get info to DR Studios to look at your issue. Also, some troubleshooting hints here.

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