Formula Property in Excel VBA Functions

[Jfarmer64]

Hello-

Trying to write an excel VBA function which will populate a range of cells with values

Here is the Code


Function setvalue(UnitPrice, Qty, Freight, R)
'R is a range of cells
'Set the sales Price
R.Cells(1).Formula = Qty * (UnitPrice + Freight)
' calculate freight
R.Cells(2).Formula = Qty * Freight * -1
'Calculate Net
R.Cells(3).Formula = R.Cells(1).Value + R.Cells(2).Value

setvalue = 0
End Function

This function seems to set the R.Cells(1) and stops. Also, the Cell which calls the function displays a #value instead of Zero.

Any help would be appreciated....

Cheers!

 

[Norie]

User defined functions aren't meant to write values to cells.

They are only really meant to return the value to the cell they are in.

Why do you even need code for what looks to me like a simple formulas.

 

[Jfarmer64]

Thanks, but I am curious if it can be done.

 

[Norie]

No it can't is the simple answer.

 

[Oaktree]

Welcome to the board.

Have you considered using a standard sub instead of trying to use a UDF?

 

[Jfarmer64]

Oaktree-

Thanks for your reply. I have considered it would like to use a UDF as I would like to pass arguments to the function to create formulas for the cells in the range.

The example I supplied was to understand the theory and methods on how to do this.

 

[Oaktree]

You still can... consider something like this:

Sub test()
Call setvalue(12.5, 100, 0.35, Range("A1:A3"))
End Sub

Sub setvalue(UnitPrice As Single, Qty As Long, Freight As Single, R As Range)
'R is a range of cells
'Set the sales Price
R.Cells(1) = Qty * (UnitPrice + Freight)
' calculate freight
R.Cells(2) = Qty * Freight * -1
'Calculate Net
R.Cells(3) = R.Cells(1).Value + R.Cells(2).Value
End Sub

 

[Richard Schollar]

Nothing wrong with using a UDF to do this as long as the UDF isn't called from a worksheet cell (ie call it from a sub within code)...

 

[Jfarmer64]

Thanks for the help and clarification. The overall attempt of this was to build a multi-sheet workbook that has the formula located on 1 tab which would have been looked up via vlookup on other tabs and populated on different sheets. The intent being 2 fold:

1.) Provide a consolidated sheet of formulas for auditiing purposes
2.) Provide a central location to add/modify formulas for sheet maintenance purposes

I will play around with this and see if I can get the results I am looking for.

Thanks for all the assistance.

Cheers!