#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 266
Default VB Syntax

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default VB Syntax

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

and in some cell:
=bumpit(A1)
--
Gary''s Student - gsnu200812


"dhstein" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default VB Syntax

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

"dhstein" wrote:

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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 266
Default VB Syntax

Thanks for the answer - that's what I needed

"Gary''s Student" wrote:

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

and in some cell:
=bumpit(A1)
--
Gary''s Student - gsnu200812


"dhstein" wrote:

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.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default VB Syntax

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.
--
Thanks,
Shane Devenshire


"dhstein" wrote:

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.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 266
Default VB Syntax

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.

"ShaneDevenshire" wrote:

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.
--
Thanks,
Shane Devenshire


"dhstein" wrote:

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
The NOW() syntax Tom Excel Discussion (Misc queries) 3 January 4th 08 05:10 PM
SQL syntax Spike Excel Worksheet Functions 2 March 8th 07 09:27 AM
VBA syntax Sunantoro Excel Discussion (Misc queries) 1 September 21st 05 03:19 AM
If then syntax RL Excel Worksheet Functions 3 June 22nd 05 05:30 AM
Syntax Help Dmorri254 Excel Worksheet Functions 2 March 2nd 05 03:51 PM


All times are GMT +1. The time now is 12:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"