VBA Excel "Compile error: Object Required"

Question

I am working on VBA code in excel and i have the following piece of code

Set strModel = Right(rng.Offset(0, 13).Value, Len(rng.Offset(0, 13).Value) - 4)

When I run the code I get a compile error to debug and it reads Object Required. What is it asking me to do?

This is a larger piece of the code:

strHSLtemp = "C:\Users\Desktop\To Do\MidDay Orders Macro Tool\Temp Files\HSL Orders Temp.xlsx"
wbHSLtemp = Dir(strHSLtemp)
Set wbHSLtemp = Workbooks.Open(strHSLtemp)
Set wsHSLtemp = wbHSLtemp.Sheets(1)
Dim arrModels() As String, strModel As String, blMultipleModels As Boolean, rngModel As range, lngModels As Long
Dim rng As range
Set strModel = Right(rng.Offset(0, 13).Value, Len(rng.Offset(0, 13).Value) - 4) 'strip off leading "HSL-"
strModel = Replace(strModel, " / ", "/") 'get rid of the spaces that appear to surround the forward slash
    If InStr(1, strModel, "/") > 0 Then 'yep, there are multiples
        blMultipleModels = True
    Else
        blMultipleModels = False
    End If
    If blMultipleModels = False Then 'just assign the model and move on in the outer loop
        wsHSLtemp.Cells(lastrowOutput, 12) = strModel

Set is used with variables which contain reference to objects. With values the Let can be used, but is optional and mostly ommited. So if you would like to be absolutely correct you can use Let strModel = "...". — Daniel Dušek
A related thread here as the root cause of the problem is same. — RBT

Pillgram Pillgram · Accepted Answer · 2015-02-17T15:08:53

You are trying to use the set keyword with a string variable. Set is only needed with Objects. Remove the set, and you should be fine:)

Specifically, Change this:

Set strModel = Right(rng.Offset(0, 13).Value, Len(rng.Offset(0, 13).Value) - 4)

To This:

strModel = Right(rng.Offset(0, 13).Value, Len(rng.Offset(0, 13).Value) - 4)

VBA Excel "Compile error: Object Required"

2 Answers