Modify variable using a sub/function

Question

I am trying to add a + sign in front of certain variables if they are positive. ex:

Sub mySub()
    Dim cash As Variant 
End Sub

It works well if I do:

Dim plus As String
plus = "+"


If cash > 0 Then
  cash = plus & cash
  Else
  cash= cash
End If

But I was looking for a sub or function that would take all my variables and add a + sign in front of them if they are positive.

sub NewSub(i As Variant)
    If i > 0 Then
       i = plus & i
       Else
       i = i
    End If
End sub

But it doesn't seem to work as it doesn't show me anything (I then display my variables in a cell in excel). And a function doesn't work either.

Any ideas on how to create a sub/function to do that? Can I loop through my variables in any way ?

Did you envisaged to use Cell Formatting?

Vincent G
– Vincent G

2016-08-30 08:45:57 +00:00
Commented Aug 30, 2016 at 8:45 — Vincent G
– Vincent G, Commented Aug 30, 2016 at 8:45

Graham · Accepted Answer · 2017-09-21 17:28:41Z

3

First off, start using Option Explicit which forces you to explicitly declare every variable and will catch mismatch errors in the VBA editor and not at runtime.

Next, if you are going to change a numerical variable to a string by prefacing a 'plus' sign onto the left end then the original variable will have to be a variant type. If you want to pass a parameter into a sub procedure and have the sub change it then the parameter must be ByRef.

Alternately, you could push the variable into a function and return the new value.

Option Explicit

Sub main()
    Dim cash As Variant, cash2 As Variant

    cash = 10
    Debug.Print cash    '10 (as number)

    AddPlus cash
    Debug.Print cash    '+10 (as string)

    cash = 10
    Debug.Print cash    '10 (as number)

    cash = udfAddPlus(cash)
    Debug.Print cash    '+10 (as string)

End Sub

Sub AddPlus(ByRef i As Variant)
    If i > 0 Then
       i = "+" & i
    Else
       i = i
    End If
End Sub

Function udfAddPlus(i As Variant)
    If i > 0 Then
       udfAddPlus = "+" & i
    Else
       udfAddPlus = i
    End If
End Function

The Debug.Print command sends output to the VBE's Immediate window.

edited Sep 21, 2017 at 17:28

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Aug 30, 2016 at 8:35

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

lo1gur Over a year ago

It works perfectly thanks! Now would you know how to loop through the different variables with the function (like tell the function to start at cash1 and stop at cashn) ?

user4039065 Over a year ago

If I had a number of cash variables to declare, I would use an array. dim cash(3) as variant produces 4 variant variables; cash(0), cash(1), cash(2) and cash(3). Loop through from LBound to UBound.

Collectives™ on Stack Overflow

Modify variable using a sub/function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related