CODEWITHSUNDEEP
vbaexcel
T Doe
T Doe

Reputation: 81

How do you make a variable your active cell?

I have used an input box to define a variant as a cell value

sub practice()

Dim Value1 as Variant

'Get Input Values
Value1 = Application.InputBox(prompt:="Click Cell Value", Title:="Specify 
Range", Type:=8)

'Select Variable as the ActiveCell
Value.Select

End Sub

The error occurs when I try to select the variable as the activecell. Any ideas how I can do this?

Upvotes: 2

Views: 3477

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

Range is an object type. When assigning object references in VBA, the Set keyword is required.

Value1 = ...

This works, only because Value1 is declared as a Variant (and thus can be literally anything) - what's happening is that it's being assigned to whatever the value of the selected cell(s) is.

That's because the Range class has a default property that essentially points to its Value member.

This default property makes this code:

Debug.Print ActiveSheet.Range("A1")

Equivalent to this code:

Debug.Print ActiveSheet.Range("A1").Value

So when you assign a Variant without the Set keyword, you're not assigning an object reference, therefore you're assigning a value. If the type (Range) didn't have a default member, the statement would raise a run-time error.

In other words you're looking at a Variant/String, or a Variant/Double, or a Variant/Error, depending on the content of the cell. And you can't invoke .Select on any of these!

The solution is therefore to Set the Value1 reference:

Set Value1 = ...

That way you're assigning to the object reference, not to whatever the value of its default property is.

Declaring the variable As Excel.Range would have made VBA better understand your intent:

Dim Value1 As Excel.Range
Value1 = ...

This would have raised run-time error 91 "Object reference (or With block variable) not Set", because you can't assign to the default member of an object reference that's set to Nothing (which is what an unset object reference will be).

Upvotes: 1

Related Questions