using cell value in VBA

[JoeS01]

It has been a very long while since I have been programming in VBA. Can anyone please give me simple examples of the following:

1. Using the value of a cell in the worksheet as a variable value in VBA,

and vice versa

2. Making the value of a cell equal to the value of a variable in VBA

 

[Jonmo1]

1 - MyVariable = Sheets("Sheet1").Range("A1").Value

2 - Sheets("Sheet1").Range("A1").Value = MyVariable


Hope that helps.

 

[JoeS01]

many thanks jonm01, much appreciated.

Can understand the first one OK, and in the 2nd example this is the formula I use for inserting a value in the cell from within VBA.

Taking it one step further, what formula do I put down in the Cell that will get a variable value from a function routine in VBA?

 

[Scott Huish]

In the second example, it is posting the value from the subroutine into the cell. In this case, you don't need a formula in the cell, the macro is putting the value there directly.

If you want a formula, then you need a UDF (user-defined function).

What are you trying to accomplish exactly?

 

[JoeS01]

Thanks HotPepper. I am trying to get a value of a UDF function in VBA called myFn(x) in to an Excel cell.

eg., is the format for the formula in the cell something like this:

= myFn(A1) where A1 refers to a cell in a worksheet

 

[Scott Huish]

This is a very simple example:

Function myfn(r As Range)
myfn = r
End Function

 

[JoeS01]

thanks again, Hotpepper. Is the way I have done the formula in the Cell correct?

 

[Scott Huish]

Yes, although depending on your function it may be more involved than that.