Newbee show cell address

[Microsoft Newsgroups]

I am having difficulty tranfering from C to VB.

How can I assign, pss, or MsgBox the string address of a particular cell.

If I use

Dim cellref as Object
cellref = activecell

Then cellref is neither the address nor the value of that cell, since
cellref is of type Object. Is this true?

I am learning. Bear with me. Please

John

 

[J.E. McGimpsey]

No, you'll get a syntax error. You need to use Set to assign an
object variable:

Set cellref = ActiveCell

You can use prebinding and assign cellref to a Range object as

Dim cellref As Range

If you want the value instead (say as a Double):

Dim cellRef As Double
cellref = ActiveCell.Value

(or cellref = ActiveCell, since the Value property is the default
property for a range)

If you want the address:

Dim cellRef As String
cellref = ActiveCell.Address

 

[Harald Staff]

Hi John

Right. Object, like Variant, is anything and everything. Range is a collection of one or
more cells. String is good old string. So choose your weapon depending on what you want
done and how much stack space you want it to use. Note also that objects and ranges needs
Set to assign things, other datatypes not. As in

Sub TwoWays()
Dim cellref As Range
Dim Celladr As String

Set cellref = ActiveCell
MsgBox cellref.Address & Chr(10) & cellref.Value& chr(10) & cellref.Formula 'and so on...

Celladr = ActiveCell.Address
MsgBox Celladr
End Sub