VBA function "Object variable missing" error. Number 91

Question

Excel keeps lighting it up with an "Object variable missing" error. Number 91.

Function GetMonthRange(sheetMonth) As Range
GetMonthRange = ActiveCell.Range("A1:AB1")
End Function

I'm pretty sure that Excel is maintaining its own clipboard.

Here's the link to the whole file.

https://github.com/okamura1967/Directors_project_sheet/blob/master/project-sheet-for-directors.vbs

As @craig has answered, the use of Set is required when you are assigning a value to a variable other than primitive ones such as integer, string, double etc. In the above example, you are returning a Range instance which is an object variable. In order to assign to such variable, you need Set. — shahkalpesh
– shahkalpesh, Commented Mar 8, 2011 at 7:00
Also, use Range("A1:AB1") or ActiveSheet.Range("A1:AB1") instead of ActiveCell.Range("A1:AB1"). Although both should work same, the former is better. — shahkalpesh
– shahkalpesh, Commented Mar 8, 2011 at 7:02

Charles Williams · Accepted Answer · 2011-03-08 10:42:16Z

3

There are several things wrong with your function. 1. If you want to return a range you have to use Set because Range is an object. 2. The parameter sheetMonth is not used 3. The function will return different results depending on whatever the activecell happens to be when the function is executed. 4. If this is a UDF it will not recalculate whenever anything in A1:B1 changes, because the A1:B1 is not a parameter.

What are you actually trying to do?

answered Mar 8, 2011 at 10:42

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

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

1 Comment

StormShadow Over a year ago

Thanks Charles! I'm trying to make a simple Gannt chart for myself and a few others at my company to track the projects we're working on. You can view the full code at the github link I posted above in my P.S. if you want. The actual code is commented out at present so I can figure out what the issue is; the code in the snippet above actually has no relevance to the project; but it was failing in the same way as the real code and I didn't want to confuse people by including the commented out part.

Craig T · Accepted Answer · 2011-03-08 06:51:28Z

1

I changed your function to:

Function GetMonthRange() As Range
   Set GetMonthRange = ActiveSheet.Range("A1:AB1")
End Function

This seems to work for me now.

answered Mar 8, 2011 at 6:51

Craig T

2,7725 gold badges25 silver badges34 bronze badges

4 Comments

StormShadow Over a year ago

Thanks - yeah, it wouldn't work if you tried it on your machine, because there is no calling parameter. So you took it out and it worked. But I've got to supply that parameter. Anyway, even when I take it out, it doesn't work on my document. I'm on 2003 too, by the way. That may be relevant.

chris neilsen Over a year ago

@StormShaddow the point of Craig's answer is the change from ActiveCell to ActiveSheet

StormShadow Over a year ago

Can't do it. I need to select relative to the active cell.

Craig T Over a year ago

The point is to use SET. And ActiveSheet.Range("A1:AB1") will return exactly the same as ActiveCell.Range("A1:AB1")

Collectives™ on Stack Overflow

VBA function "Object variable missing" error. Number 91

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related