User defined functions
 

Hello,

I have an Excel spreadsheet that has a column of cells
with lots of comments. I'd like those comments in their
own field. My goal is to convert this spreadsheet to an
Access table.

In an earlier post I was told that only possible with VBA
and a user defined function with the following:

public function get_comment(rng as range)
dim res
on error resume next
res=rng.comment.text
on error goto 0
get_comment=res
end function

Then use the following formula on your worksheet:
=GET_COMMENT(A1)

Can someone descrite to me how to create the user defined
function?

Thank you in advance.

Ellen

User defined functions
 

Hello,
I wrote too soon. The script is working beautifully!
-----Original Message-----
Hello,

I have an Excel spreadsheet that has a column of cells
with lots of comments. I'd like those comments in their
own field. My goal is to convert this spreadsheet to an
Access table.

In an earlier post I was told that only possible with VBA
and a user defined function with the following:

public function get_comment(rng as range)
dim res
on error resume next
res=rng.comment.text
on error goto 0
get_comment=res
end function

Then use the following formula on your worksheet:
=GET_COMMENT(A1)

Can someone descrite to me how to create the user defined
function?

Thank you in advance.

Ellen

.

User defined functions
 

Here's how:

1. Open the Visual Basic Editor (from Tools menu, select Macro, the
Visual Basic Editor).
2. Hit Ctrl-R to make sure the Project Explorer window opens (it shoul
already be open). This is a window which should show the open workboo
names and the sheet names in the workbooks in tree form.
3. Right-click on the workbook you want the function in and selec
Insert -- Module.
4. Paste the code you have there.

Note that the function will only be available when the workbook is ope
(i.e. you cannot use it in another workbook). If you need to use it i
another workbook, you would put the code in your personal macr
workbook (I'll explain if you need).



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26683