Alright...

So I created a strange little query that takes an abbreviated currency amount and converts it into raw numbers. It you were to enter $2.5k, it would give you "2500" in return.

Unfortunately, I'm having an issue passing values into the function. Previously, I was just having an InputBox let me test the values that went in and out, but I need it to accept the value from another function that loops through columns in a table.

How do I get my code to pass by reference or value? Whenever I run the following code, all I get back is the value I entered. So if I entered "$2.5k", I get back "$2.5k"

Private Sub Code_Test_Click()
Code_Click
End Sub

Function Code_Click()
Dim Amount As String
Amount = InputBox("#", "#")
Convert_NumAbbreviations_to_Numbers (Amount)
MsgBox (Amount)
End Function

Function Convert_NumAbbreviations_to_Numbers(ByRef Amount As String) 'Prototype
'Dim Amount As String
Dim CheckVar As Integer
Dim LoopValue As Integer
Dim MorK As String
Dim DecimalPlace As Integer

'Amount = InputBox("What value?", "Value")
DecimalPlace = 0

CheckVar = InStr(1, Amount, "M")
If Not IsNull(CheckVar) And CheckVar <> 0 Then
    MorK = "M"
    Amount = Replace(Amount, "M", "")
Else
    CheckVar = InStr(1, Amount, "K")
    If Not IsNull(CheckVar) And CheckVar <> 0 Then
        MorK = "K"
        Amount = Replace(Amount, "K", "")
            Else
                MorK = "None"
End If
End If

CheckVar = InStr(1, Amount, "$")
If Not IsNull(CheckVar) Then
    Amount = Replace(Amount, "$", "")
    End If
    
LoopValue = Len(Amount)
Do While (LoopValue <> 0)
    CheckVar = InStr(1, Amount, ",")
    If Not IsNull(CheckVar) Then
        Amount = Replace(Amount, ",", "")
    End If
    LoopValue = LoopValue - 1
Loop

CheckVar = InStr(1, Amount, ".")
If Not IsNull(CheckVar) And CheckVar <> 0 Then
    DecimalPlace = Int(Len(Amount)) - CheckVar
    Amount = Replace(Amount, ".", "")
End If

If MorK = "M" Then
    DecimalPlace = Abs(DecimalPlace - 6)
Else
    If MorK = "K" Then
        DecimalPlace = Abs(DecimalPlace - 3)
End If
End If

Do While DecimalPlace <> 0
    Amount = Amount + "0"
    DecimalPlace = DecimalPlace - 1
Loop

End Function

Thanks.

Edited 6 Years Ago by Lygris: n/a

Ah yes... One of the funny (or not so) undocumented things about VB... Your call to the procedure with the parens wrapped around the variable forces the value to be passed byvalue instead of byref...

Convert_NumAbbreviations_to_Numbers (Amount) 'forced byval
Convert_NumAbbreviations_to_Numbers Amount 'normal byref

Good Luck

When you create a Function, set the return value to the function name to return it:

Function GetValue(aVal as Integer, bVal as Integer) as Integer
...
    GetValue = answer
End Function

Use: newValue = GetValue(a,b)

This question has already been answered. Start a new discussion instead.