in vba I am getting a Object variable not set (Error 91)

Question

This is a basic error or lack of understanding on my part. I've searched a number of questions here and nothing seems applicable.

Here is the code

Option Explicit

Public Function ReturnedBackGroundColor(rnge As Range) As Integer
    ReturnedBackGroundColor = rnge.Offset(0, 0).Interior.ColorIndex
End Function

Public Function SetBackGroundColorGreen()
    ActiveCell.Offset(0, 0).Interior.ColorIndex = vbGreen
End Function

Public Function CountBackGroundColorGreen(rnge As Range) As Integer
    Dim vCell As Range

CountBackGroundColorGreen = 0

For Each vCell In rnge.Cells
    With vCell
        If ReturnedBackGroundColor(vCell) = 14 Then
            CountBackGroundColorGreen = CountBackGroundColorGreen + 1
        End If
    End With
Next
End Function

Public Function GetBackgroundColor() As Integer
Dim rnge As Range

GetBackgroundColor = 3
rnge = InputBox("Enter Cell to get Background color", "Get Cell Background Color")

GetBackgroundColor = ReturnedBackGroundColor(rnge)
End Function

I was adding the last function and everything else was working prior to that and am getting the error on the first statement in that function.

For the error, one of the possible fixes is to add a reference the proper library. I don't know what is the proper library to be referenced and cannot find what library the InputBox is contained. It's an activeX control but I don't see that in the tools->reference pull down. I do have microsoft forms 2.0 checked.

I've tried various set statements but I think that the only object that I've added is the inputbox.

Any suggestions?

thanks.

Answer 1

Use application.inputbox, add the type as range and Set the returned range object.

Option Explicit

Sub main()
    Debug.Print GetBackgroundColor()
End Sub

Public Function GetBackgroundColor() As Integer
    Dim rnge As Range        
    Set rnge = Application.InputBox(prompt:="Enter Cell to get Background color", _
                                    Title:="Get Cell Background Color", _
                                    Type:=8)        
    GetBackgroundColor = ReturnedBackGroundColor(rnge)
End Function

Public Function ReturnedBackGroundColor(rnge As Range) As Integer
    ReturnedBackGroundColor = rnge.Offset(0, 0).Interior.ColorIndex
End Function