Built in function referencing Worksheets

Dunkay

New Member
Joined
Jan 14, 2004
Messages
3
:oops:

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

I have simplified my problem in the following code:

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!
Code:
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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

Active Member
Joined
Nov 26, 2003
Messages
440
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

New Member
Joined
Jan 14, 2004
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,441
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top