(Excel VBA) Variable doesn't work and brings up "Object Required error"

Question

So I was testing a simple program to find the letter e and remove it from the cell. Except that Variable A doesn't work even though Cells(1,1) does.

Sub remove()
  A = Cells(1, 1)
  pos = InStr(A, "e")
       'A works for pos though I don't know why
  'A.characters(pos,1).delete'
       'The above doesn't work for some reason'   
  Cells(1, 1).Characters(pos, 1).delete    
End Sub

Things that I have tried

Dim A as Range
Using Range

Also Sorry for not being more specific about my problem. I'm not sure about the terminology behind VBA and coding in general though I'm working on it

I suggest you always use Option Explicit

Vincent G
– Vincent G

2018-06-06 07:24:45 +00:00
Commented Jun 6, 2018 at 7:24 — Vincent G
– Vincent G, Commented Jun 6, 2018 at 7:24

MikeFromCanmore · Accepted Answer · 2018-06-06 04:24:29Z

1

Jeeped beat me to it. VBA is not strongly typed, so unless you explicitly tell it that the variable you are assigning is an object-type by using 'Set', the value that A takes is, in this case, the contents of the cell instead of the cell itself.

You don't even need to Dim it, although it is good practice.

Sub remove()
  Set A = Cells(1, 1) 'Need to use Set
  pos = InStr(A, "e")

  'This works just fine now
  A.characters(pos,1).delete'
End Sub

answered Jun 6, 2018 at 4:24

MikeFromCanmore

1629 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ka Fai Over a year ago

I get it I think. So I'm making A=Cells(1,1).value by not typing set. Thanks very much :)

user4039065 · Accepted Answer · 2018-06-06 04:14:21Z

0

You're assigning cells(1, 1).value to A. If you want the range object properties of cells(1, 1) (like 'characters') you need to Set a range-type var to cells(1,1).

Sub remove()
  dim A as range
  set A = Cells(1, 1)
  pos = InStr(A.value, "e")
  A.characters(pos,1).delete'   
End Sub

answered Jun 6, 2018 at 4:14

user4039065

Collectives™ on Stack Overflow

(Excel VBA) Variable doesn't work and brings up "Object Required error"

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related