VB Syntax

[dhstein]

I'm trying to write a User Defined Function and need the VB syntax to refer
to the cell. For example if my function is called func1 and I put the
formula =func1(B2) in my spreadsheet, I want to manipulate the contents of B2
in my function.

What is the syntax to read/access the contents of cell B2 in my VB function?


For example:

function func1(Entry As Long)
CellContents = ?????????????
func1 = CellContents + 1
End Function

Thanks.

 

[Gary''s Student]

Function bumpit(r As Range) As Long
bumpit = r.Value + 1
End Function

and in some cell:
=bumpit(A1)

 

[Mike H]

Hi,

I would suggest you give your function a more intuative name because func1
won't mean much in 12 months time. Try this

Function func1(rng As Range) As Long
func1 = rng.Value + 1
End Function

Mike

 

[Don Guillett]

try this =func1(b2)

function func1(x As Long)
application.volatile
func1 = x + 1
End Function

 

[dhstein]

Thanks for the answer - that's what I needed

Function bumpit(r As Range) As Long
bumpit = r.Value + 1
End Function

and in some cell:
=bumpit(A1)

 

[ShaneDevenshire]

Hi,

You might use something like this

Function CountThis(R As Double) As Double
CountThis = R + 1
End Function

However, I ask why you would do this in a function if Excel already does
this? Of course I realize this is just an example, but maybe you should tell
us what you really want and we would provide more useful feedback.

I replaced Long with Double, but that may or may not be acceptable.

 

[dhstein]

Thanks Shane. I wrote that function successfully. I wrote a function to
test if a cell was a valid name field - not blank - not void - not numeric -
etc. So that worked fine. Now I'm trying another one - which I will pose as
a new question in the forum.