CODEWITHSUNDEEP
vbaexcelvisio
Preston
Preston

Reputation: 8187

VBA .AddPicture with late binding Error 424

Using the below code i get a 424 error "Object Required" on the .AddPicure line as indicated. I'm unsure as to why as pic is dimensioned as object, and the .addpicture comand looks fully referenced to me.

Apologies for the length of code, i thought it best to leave in all variables.

I'm using Excel 13 from MS Visio 16, and late binding is necessary.

**Edit: Sorry, it is infact an add text box line thats giving me the problem, I've updated the code below...

Sub testexcel()

Dim pic As Object
Dim rng As Object
Dim tWidth As Long, tHeight As Long

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.workbooks.Open("C:\Users\tom\Desktop\Book1.xlsx")
Set xlWs = xlWb.sheets("Sheet1")

xlApp.ScreenUpdating = False
Set rng = xlWs.Range("B18")
Set rng2 = xlWs.Range("A1", rng.Offset(-1, -1))

picture1 = "C:\Users\tom\Desktop\PX001.bmp"

pHeight = 145
pWidth = 200
tHeight = 10
tWidth = 200
posX = 10
posY = 10


'On Error GoTo ErrMsg
With xlWs.Range("A1", rng.Offset(-1, -1))
'*******Problem on next line*******
    Set txtBx = xlWs.Shapes.AddTextbox(msoTextOrientationHorizontal, 
txtPosX, txtPosY, tWidth, tHeight).TextFrame.Characters.Text = "FooBar"
End With
'Some other code here...
End Sub

Upvotes: 0

Views: 192

Answers (1)

arcadeprecinct
arcadeprecinct

Reputation: 3777

try splitting it up

Set txtBx = xlWs.Shapes.AddTextbox(msoTextOrientationHorizontal, txtPosX, txtPosY, tWidth, tHeight)
txtBx.TextFrame.Characters.Text = "FooBar"

I think this is what's happening:

xlWs.Shapes.AddTextbox(msoTextOrientationHorizontal, txtPosX, txtPosY, tWidth, tHeight).TextFrame.Characters.Text = "FooBar"

This retunrs false because the second = is interpreted as a comparison. Then you are basically doing Set txtBx = False which causes the error.

It could also be that vba tries to assign the Text property which is a string to txtBx.

edit: I would also suggest using Option Explicit. If VBA knows that txtBx is supposed to be a shape, it tells you it got a type mismatch. In this case you got lucky because the Set tells it to expect an object and thus threw an error. If you wanted to assign a string for example, you would have gotten the error at a later line (or no error at all) because you have False where you expect a string which makes debugging more complicated.

Upvotes: 1

Related Questions