Built in function referencing Worksheets

[Dunkay]

I have written macros before but this built-in function is evading me!

I have simplified my problem in the following code:

Function print_name(p_name)

ActiveSheet.Range("h1").Value = p_name
print_name = p_name

End Function

If I run this simple function using a macro, it works!

Sub test1()
print_name ("Colm")
End Sub

When I try to use it as a formula in Excel, it doesn't (Returns: #VALUE)
=print_name("Colm").

The real function is manipulating some data from a couple of other worksheets, carrying out a few calcs & then returns a value. I do not want to use a macro to run it, I want to be able to use a UDF.

Help!

 

[Juan Pablo González]

A function, called from a spreadsheet, can only return a value, it cannot change the value of other cells... that's why the function is failing.

 

[sbendbuckeye]

I don't know what your technical background is, but if you are willing to write some VBA code, right click on ThisWorkBook in the VBE and select View Code. If you click the left combobox at the top (default shows (General) and select WorkBook then the combobox on the right will change to display the events you can react to with your code.

If you select SheetSelectionChange it gives you this header:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

You can use Target.Column and Target.Row to determine if the user clicked on a specific cell and react accordingly. Perhaps other events would suit your purposes better.

I hope this was helpful and Good LucK!

 

[Dunkay]

Re: Built in function referencing Worksheets - RESOLVED

Thanks Guys.

I gues I'll have to try a different approach.
There's nothing for it now but to roll up the sleeves and get stuck into coding the data manipulation.