Excel Custom Function with parameters

Question

I am trying to create a custom function in an excel module as next:

Function STATUS(valuex As String)

    If ActiveCell.Offset(0, 1).Value = valuex Then

    ActiveCell.Value = ActiveCell.Offset(0, -1).Value

    'Remove value from left column
     Activecell.offset(0,1).clearcontents

   End If

End Function

it basically would do this:

Number  Result  Status
          11    System
22              Type
          33    System
          44    System
55              Hardware
66              Type
          77    System
          88    System
99              Software
110             Type
         121    System
         132    System
143             Hardware
154             Type
165             Type
         176    System
187             Hardware
198             Type
209             Software

If right cell = valuex (the string "System" for instance) is like valuex then put left cell value in the formula/function cell and delete left column value.

But whatever I program all it returns is a zero (0) or a Name# error.

Please help

Answer 1

You're writing a User-Defined Function (UDF), i.e. a Public Function in a public, standard module, that can be invoked from a worksheet cell - and this particular type of function has a particular set of constraints. For instance, it is not allowed to have side-effects. A UDF takes some input, processes it, and then returns a result.

So the signature of a UDF should look like this:

Public Function {name}({args}) As {type}

The very first thing to think about when you write a function, is what do you need it to return - in other words, what is it that the cell that says =MYFUNCTION(A1,A2) should contain after it's calculated. For example if you wrote an Add function that adds two Double values together, you'll want it to return a Double:

Public Function Add(ByVal value1 As Double, ByVal value2 As Double) As Double

The body of a function computes the result from the given parameters:

    Dim result As Double
    result = value1 + value2

Before it returns/exits, you need to assign the function's return value. That's done by assigning to the function's identifier:

    Add = result

Excel's calculation engine then takes that result, and that's how a cell with a formula such as =Add(2, 2) ends up with a value of 4.

---

Your STATUS function is dependent on ActiveCell, which is whatever cell that's currently selected on the ActiveSheet: it is NOT the cell that invoked the function. Recalculating the workbook with some random cell selected would likely yield broken results, if anything.

Being a UDF, it isn't allowed to .ClearContents on a cell (or affect any other cell in any way) - that's why the function is returning a #NAME? error for the execution path that enters the conditional block, and since no return value is ever assigned, the other execution path yields 0, which is the numeric representation of an Empty variant, which is what your function currently returns.

If a UDF needs to know about another cell's value, the best thing to do is to take that cell's value as a parameter: that way the function works, without any assumptions about the layout of the worksheet. How useful would VLOOKUP be if it didn't take a lookup_value argument and instead took that value from the .Offset(0, 1) cell? There would be riots!

---

When your requirement is to do something rather than calculate/compute something, what you need isn't a UDF, but a macro.

A macro is a parameterless Public Sub procedure in a public, standard module (or a Worksheet module), that can be invoked from the "Macros" window, or executed when the user clicks a Shape, an ActiveX CommandButton, or they can be assigned to the OnAction property of some custom menu item - whatever rocks your boat.

Sub procedures do something, they're actions. They can access and alter global state, modify any cell, worksheet, or workbook; they can even spawn an instance of PowerPoint and paste a chart as a picture onto a new Slide - anything you could possibly think of, sky's the limit!

Since what you need here is something that does stuff, the code you need to write needs to be more like a macro. Don't call it STATUS; use a verb and describe what it's doing: you're moving values from one column to another, based on a given criteria. When you write a Sub procedure, think first of how you want to invoke it.

I think something like this would be neat:

MoveValues Sheet1.Range("$B$2:$B$22"), "System"

So the signature would look like this:

Private Sub MoveValues(ByVal Target As Range, ByVal Criteria As String)

And the body can now traverse the specified Target range, evaluate whether the cell to the right matches the Criteria, then accordingly move the value to the left. Or better - how about we don't assume a worksheet layout at all, and invoke it like this:

With Sheet1
    MoveValues .Range("A2:A22"), .Range("B2:B22"), .Range("C2:C22"), "System"
End With

Now if we ever need to insert a column between A and B, or between B and C, we only need to change the arguments we're passing to our procedure, rather than the procedure itself!

Private Sub MoveValues(ByVal Source As Range, ByVal Target As Range, ByVal Status As Range, ByVal Criteria As String)

But first, we need to validate our assumptions, and decide what to do when our expectations aren't met - we need single-column ranges with an equal number of rows!

In many cases, raising a run-time error is the best thing to do. Error #5 "invalid procedure call or argument", seems a rather good fit:

    If Source.Columns.Count <> 1 Or Target.Columns.Count <> 1 Or  Status.Columns.Count <> 1 Or _
       Source.Rows.Count <> Target.Columns.Count Or _
       Status.Rows.Count <> Target.Columns.Count _
    Then
        Err.Raise 5
    End If

We can even customize the error message, to help us debug the calling code later, when we change the parameters 6 months down the line and forgot everything about the assumptions of that MoveValues procedure:

    If Source.Columns.Count <> 1 Or Target.Columns.Count <> 1 Or  Status.Columns.Count <> 1 Or _
       Source.Rows.Count <> Target.Columns.Count Or _
       Status.Rows.Count <> Target.Columns.Count _
    Then
        Err.Raise 5, "MoveValues", _
            "Source, Target, and Status ranges must be 1 column and the same number of rows."
    End If

We also need to verify that our Criteria isn't empty, or just whitespace!

    If Trim$(Criteria) = vbNullString Then
        Err.Raise 5, "MoveValues", "Criteria string cannot be empty or whitespace."
    End If

Now that we've validated our inputs, the rest of the procedure can safely assume the Source, Target and Status ranges are all single-column ranges, that they're all the same size, and that we have a valid criteria to work with. So we can proceed to iterate cells and do our thing:

    Dim current As Long
    For current = 1 To Target.Rows.Count
        If Status.Cells(current).Value = Criteria Then
            Target.Cells(current).Value = Source.Cells(current).Value
            Source.Cells(current).ClearContents 
        End If
    Next

Now all that's left to do is to write a macro that invokes it:

Public Sub MoveSystemValues()
    With Sheet1
        MoveValues .Range("A2:A22"), .Range("B2:B22"), .Range("C2:C22"), "System"
    End With
End Sub

And now we can run that MoveSystemValues macro from Excel's Macros window, or assign MoveSystemValues to some Shape or button... and then realize that it works pretty well for a small number of rows, but is rather slow given larger ranges - but we have enough to chew on for now.

Answer 2

You cannot do what you want with an UDF (User defined Function).

You are starting in VBA, so you need to know the main difference between SUBS and FUNCTIONS.

Subs perform actions on your Excel file (actions like selecting, clearing contents, changing worksheets, opening another workbook, doing calculations and many many more).

Functions return a value, but don't perform any actions. Think about them as your own formula Excel. Formulas in Excel don't do actions, they just return a value based on some arguments.

More info here: http://excelhints.com/2009/02/12/difference-between-sub-and-function/

So you need to rewrite your code as a sub procedure and then execute it :)

UPDATE: What I said in prior lines is not 100% true always. I posted that because for a newcomer in developing code with VBA I think it's a good start point. When you are a newbie, and from my point of view, I think it's easier to learn using only subs to make actions and using only functions to get customized calculations. And then, with some experience, go to next level and start combining them and use them to do more complicated stuff. Subs and Functions can be combined when executing code.

When you call an UDF from a sub, it can perform some actions (like deleting a sheet). And I said some actions because to be honest, I don't know if all actions are available.

But if you call the UDF from a cell, typing it as a normal Excel formula, then it won't perfom any action.

An example:

Public Function DeleteWorkSheet() As Boolean
Sheets(3).Delete
End Function

This UDF will delete the third worksheet of my workbook.

To call it from a sub it would be:

Sub Macro1()
DeleteWorkSheet
End Sub

And yes, it would delete the third worksheet.

But If I call this UDF from a cell like a normal Excel Formula, then it will do nothing.

Hope this clarification helps.