how do i set a value to a range using a function from module in excel vba?

Question

hello all,

i want to do a simple function in a module in excel vba, so i can use it as a custom function in excel. (i use excel 2003, or 2007 , it's doesnt matter)

i create a function(!) in a new workbook and it's looks like this:

Function a()
    Sheets(1).Range("A1").Value = 4
end function

but when i try to use it on the sheet1 it's wont work! i tried many things.

how can i make this work (with no workarounds, i want to use it as a custom function) ?

please help. thanks, gadym

Answer 1

A function that uses other cells cannot become a formula function (UDF) in Excel, because it breaks Excel's dependency model (all cell dependencies must be explicit in the formula). However a formula can use cell values as inputs.

Here is a simple function added to a module in VBA:

Public Function testFunction(inputValue As Integer) As Integer

    testFunction = inputValue * 2

End Function

This can be used in any cell formula. For example, =testFunction(4) or =testFunction(A5).

EDIT

Okay, reviewing the comment you made against guitarthrower's answer. A formula can only send an answer to the cell it is in - it cannot send an answer to a different cell. Therefore, if a formula is your only choice, you must have a formula in cell A1 that reads input from C1.

Formula in A1:

=a(C1)

Function in module:

   Public Function a(string col)
       a = iif(col = "ok", "1", "2")
   End Function

However, if there is a problem putting a formula into A1, you are left with a manually driven process (a sheet button, a toolbar button etc. to push these values) or a worksheet cell change event. The downside of a worksheet event is that it fires off every single cell change, so you need to keep the code light and not do any heavy duty work - or if you do, make it rare.

You would add a new subroutine to the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

This is fired whenever cells are changed; the changed cells are indicated by the Target range. Your code would update A column cells if the Target contained C column cells. This is more work than the formula approach, but it does render the process entirely automatic. I have no access to Excel right now so the following is just from memory and Googled fragments, untested:

For Each cCell In Application.Intersect(Target, Me.Range("C1:C5"))
   cCell.Offset(, -2).Value = iif(cCell.Value = "ok", "1", "2")

I would prefer not to use explicit ranges, but rather a named range as it makes your code less fragile to change. But that would make finding the corresponding A column cell a little more tricky to determine and I'm not going to attempt that code without Excel to hand =)

Answer 2

It looks like you're trying to programatically change the value of an cell within your spreadsheet. This can be done, but as Joel Goodwin pointed out, this can't be done with a "User-Defined Function." However, you can do this with a macro or by adding a button to the sheet and putting code in the button.

For example, add a button to your sheet by going to View > Toolbars > Control Toolbox (this will be different for newer versions of Excel) and selecting the button control and adding it to your sheet. Once the button is added, double click on it. This should bring up the Visual Basic editor. Put your code in the body of the Sub that it provides, like this:

Private Sub CommandButton1_Click()
  Sheets(1).Range("A1").Value = 4
End Sub

Go back to your worksheet and disable design mode in the control toolbox (it's the icon with the light blue triangle in it). With design mode disabled, click the button and the value of cell A1 will change to 4.

Answer 3

If cell A1 contains the formula =a(), and you want A1 to show 4 (or any other cell with the formula =a() in it) then you need to change your code to this:

Function a()
    a = 4
End Function