Error Object Variable Not Set when not using Set

Question

I keep running into the error message "Object Variable or With Block Variable Not Set", however, when I use SET to set the variable then I get the same error despite having set it. Does anyone know why this may be happening? This is my code I have and the line that keep throwing the two errors is

name = sht.Range("A1:Z2").Find(What:=colname, Lookat:=xlWhole, LookIn:=xlFormulas, MatchCase:=True)

The error gets thrown so early that the code cannot even try running getdata so I have not yet been able to debug that to see if it works entirely. Before calling getdata, the function getcolumnindex runs fine. Here is my entire code

Public Sub Main() 

Dim wb As Workbook, ws As Worksheet, i As Range, dict As Object, wbSrc As Workbook 

Dim value As Long 

Set wb = ThisWorkbook 
Set ws = wb.Worksheets("Sheet1") 

Set dict = CreateObject("scripting.dictionary") 

For Each i In ws.Range("E2:E15").Cells 
 sysnum = i.value 
 sysrow = i.Row 
 syscol = i.Column 

getcolumnindex ws, "Range (nm)" 
value = getdata(sysrow, "Range (nm)") 

Next i 

End Sub

Function getcolumnindex(sht As Worksheet, colname As String) As Integer 

Dim name As Range, colind As Integer 
## Error gets thrown at this line below 
Set name = sht.Range("A1:Z2").Find(What:=colname,Lookat:=xlWhole, LookIn:=xlFormulas, MatchCase:=True) 
   If Not name Is Nothing Then 
        colind = name.Column 
        MsgBox name & " column index is " & colind 
   End If
End Function

Function getdata(WDrow As Integer, parametercol As String) 

Dim cell As Variant, ws As Worksheet 

cell = getcolumnindex(ws, "Tuning Range (nm)") 
Data = Cells(WDrow, parametercol) 
MsgBox (Data) 

End Function

@BigBen It does find the `colname` as it works when it is on its own. Its when combined with `getdata` does it throw the error. I know that it does because the messagebox appears printing the column index — , Oct 07 '22 at 16:57
@BigBen So I can write `Set name = sht.Range("A1:Z2").Find(What:=colname, LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=True) — , Oct 07 '22 at 17:00
@BigBen Unfortunately, even with adding the two lines it did not get rid of the error — , Oct 07 '22 at 17:04
@resu you are setting a value to `Data` instead of `getdata` For the Function to return anything. That has been the issue all along. change `Data = Cells(WDrow, parametercol) ` to `getdata = Cells(WDrow, parametercol)` — Ricardo A, Oct 07 '22 at 17:12
@BigBen adding the message box has it return False then the column index message box and then true. — , Oct 07 '22 at 17:12
@BigBen I would like for the result of getcolumnindex to be used in the getdata function as one of the inputs. — , Oct 07 '22 at 17:13
@RicardoA Sadly, the error "Object Variable or With Block Variable not Set" still occurs — , Oct 07 '22 at 17:14
Because both functions are doing the same thing, Basic function behavior: for a function to return something assign a value to the function name. example: Function GiveMeData() GiveMeData = `Returned Data' End Function, you are not using the Function name, therefor nothing is being returned. — Ricardo A, Oct 07 '22 at 17:20
@RicardoA So I have to write another function to assign value? I cant just write `parametername = getcolumnindex(ws, "Range (nm)") — , Oct 07 '22 at 17:30

BigBen · Answer 1 · 2022-10-07T19:31:59.877

0

There are many issues. Among them, in getdata,

ws is not Set.
Cells(WDrow, parametercol) implicitly refers to the ActiveSheet, but really needs to have its parent worksheet specified.
MsgBox (Data) should not have any parentheses.
Data is an undeclared variable.

Function getdata(WDrow As Integer, parametercol As String) 

   Dim cell As Variant, ws As Worksheet 

   cell = getcolumnindex(ws, "Tuning Range (nm)")  'ws is never Set
   Data = Cells(WDrow, parametercol)
   MsgBox Data 

End Function

It would be good to read up on the difference between Subs and Functions, and how to return a value from a Function.

getcolumnindex returns 0, always. If you want getcolumnindex to return something, you need the line

getcolumnindex = ...

within the function.

The following line does nothing with the return value of getcolumnIndex.

getcolumnindex ws, "Range (nm)"

This does nothing with the return value of getcolumnIndex.

edited Oct 07 '22 at 19:31

answered Oct 07 '22 at 17:14

BigBen

46,229
7
24
40

I would highly suggest reading up on the difference between `Sub`s and `Function`s, and how to return a value from a `Function`. None of your functions are returning what you think they are. – BigBen Oct 07 '22 at 17:18
So I should format it to be similar to `value = getdata(sysrow, "Range (nm)")` – Oct 07 '22 at 17:22
First read [this](https://stackoverflow.com/questions/2781689/how-to-return-a-result-from-a-vba-function). – BigBen Oct 07 '22 at 17:23
Using the readings I have made this change `Function getcolumnindex(sht As Worksheet, colname As String) As Integer Dim paramname As Range Set paramname = sht.Range("A1:Z2").Find(What:=colname, Lookat:=xlWhole, LookIn:=xlFormulas, MatchCase:=True) If Not paramname Is Nothing Then getcolumnindex = paramname.Column MsgBox paramname & " column index is " & getcolumnindex End If End Function` – Oct 07 '22 at 17:27

Error Object Variable Not Set when not using Set

1 Answers1