Function procedure (VBA)

[Deuce Bucksman]

Hello, gurus.

Quick question: I'm trying to get a better understanding of how function procedures work in VBA and I hit a little snafu. I'm trying to come up with a formula that will take its arguement and convert it to a negative (if it's already a negative then convert to positive). I just can't figure out why it's not working. Can anyone shed any light on this?

Function Multiple()
For Each cell In Selection
cell = cell * -1
End Function

 

[njimack]

A function works just like a standard formula - it will return a value. You need a procedure, not a function. Something like this:

Sub Multiple()
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In Selection
cell.Value = -cell.Value
Next cell
Application.ScreenUpdating = True
End Sub

 

[JackDanIce]

A function (non CSE) works on a single argument, so below for a single cell (argument) would be:

Function Multiple(x as Integer)
Multiple = -x
End Function

Try entering 1 in cell A1 and in cell B1 enter

=Multiple(A1)

Neil's suggestion works as a macro for a range of values

 

[Deuce Bucksman]

Thanks, all. Both work. So can functions work with arrays? Or would one need a procedure for that? Thanks so much for your help!

 

[EB08]

Functions can definitely work with arrays...not sure if this sample will be relevant to the direction you're looking to go or not, but it often helps to have something in front of you as an example.

Public Function cArr(rngR1 As Range, rngR2 As Range)
'/* Sample array function used to bring two non-adjacent ranges into a single array */
Dim arrTemp()
ReDim arrTemp(0)
Dim rngCell As Range

'/* Fill temporary array with the results of the first user selection */
    For Each rngCell In rngR1
        arrTemp(UBound(arrTemp())) = rngCell
        ReDim Preserve arrTemp(UBound(arrTemp()) + 1)
    Next rngCell
'/* Fill temporary array with the results of the second user selection */
    For Each rngCell In rngR2
        arrTemp(UBound(arrTemp())) = rngCell
        ReDim Preserve arrTemp(UBound(arrTemp()) + 1)
    Next rngCell
'/* Set cArr (name of function) equal to the newly constructed temporary array arrTemp() */
    cArr = arrTemp
    
End Function