Object Required Error VBA Function

Question

I've started to use Macros this weekend (I tend to pick up quickly in regards to computers). So far I've been able to get by with searching for answers when I have questions, but my understanding is so limited I'm to a point where I'm no longer understanding the answers. I am writing a function using VBA for Excel. I'd like the function to result in a range, that can then be used as a variable for another function later. This is the code that I have:

Function StartingCell() As Range

Dim cNum As Integer
Dim R As Integer
Dim C As Variant

C = InputBox("Starting Column:")
R = InputBox("Starting Row:")

cNum = Range(C & 1).Column

Cells(R, cNum).Select

The code up to here works. It selects the cell and all is well in the world.

Set StartingCell = Range(Cell.Address)
End Function

I suppose I have no idea how to save this location as the StartingCell(). I used the same code as I had seen in another very similar situation with the "= Range(Cell.Address)." But that's not working here. Any ideas? Do I need to give more information for help? Thanks for your input!

Edit: I forgot to add that I'm using the InputBox to select the starting cell because I will be reusing this code with multiple data sets and will need to put each data set in a different location, each time this will follow the same population pattern.

---

---

Thank you A.S.H & Shai Rado

I've updated the code to:

Function selectQuadrant() As Range

Dim myRange As Range
Set myRange = Application.InputBox(Prompt:="Enter a range: ", Type:=8)

Set selectQuadrant = myRange


End Function

This is working well. (It appears that text is supposed to show "Enter a range:" but it only showed "Input" for the InputBox. Possibly this could be because I'm on a Mac?

Anyhow. I was able to call the function and set it to a new variable in my other code. But I'm doing something similar to set a long (for a color) so I can select cells of a certain color within a range but I'm getting all kinds of Object errors here as well. I really don't understand it. (And I think I'm dealing with more issues because, being on a mac, I don't have the typical window to edit my macros. Just me, basically a text box and the internet.

So. Here also is the Function for the Color and the Sub that is using the functions. (I've edited both so much I'm not sure where I started or where the error is.)

I'm using the functions and setting the variables to equal the function results.

Sub SelectQuadrantAndPlanets()

Dim quadrant As Range
Dim planetColor As Long

Set quadrant = selectQuadrant()
Set planetColor = selectPlanetColor() '<This is the row that highlights as an error

Call selectAllPlanets(quadrant, planetColor)

End Sub

This is the function I'm using to select the color that I want to highlight within my range
I would alternately be ok with using the interior color from a range that I select, but I didn't know how to set the interior color as the variable so instead I went with the 1, 2 or 3 in the input box.

Function selectPlanetColor() As Long

Dim Color As Integer

Color = InputBox("What Color" _
& vbNewLine & "1 = Large Planets" _
& vbNewLine & "2 = Medium Planets" _
& vbNewLine & "3 = Small Planets")

Dim LargePlanet As Long
Dim MediumPLanet As Long
Dim smallPlanet As Long
    LargePlanet = 5475797
    MediumPlanet = 9620956
    smallPlanet = 12893591


If Color = 1 Then
    selectPlanetColor = LargePlanet
Else
    If Color = 2 Then
        selectPlanetColor = MediumPlanet
    Else
        If Color = 3 Then
            selectPlanetColor = smallPlanet
        End If
    End If
End If


End Function

Any help would be amazing. I've been able to do the pieces individually but now drawing them all together into one sub that calls on them is not working out well for me. Thank you VBA community :)

Answer 1

It's much simpler. Just

Set StartingCell = Cells(R, C)

after getting the inputs, then End Function.

The magic of the Cells method is it accepts, for its second parameter, both a number or a character. That is:

Cells(3, 4) <=> Cells(3, "D")

and

Cells(1, 28) <=> Cells(3, "AB")

One more thing, you can prompt the user directly to enter a range, with just one input box, like this:

Dim myRange as Range
Set myRange = Application.InputBox(Prompt:="Enter a range: ", Type:=8)

The Type:=8 specifies the input prompted for is a Range.

Last thing, since you are in the learning process of VBA, avoid as much as possible:

• using the Select and Activate stuff

• using unqualified ranges. This refers to anywhere the methods Cells(..) or Range(..) appear without a dot . before them. That usually leads to some random issues, because they refer to the ActiveSheet, which means the behavior of the routine will depend on what is the active worksheet at the moment they run. Avoid this and always refer explicitly from which sheet you define the range.

Answer 2

Continuing your line of thought of selecting the Range bu Selecting the Column and Row using the InputBox, use the Application.InputBox and add the Type at the end to restrict the options of the user to the type you want (Type:= 1 >> String, Type:= 2 >> Number).

Function StartingCell Code

Function StartingCell() As Range

Dim cNum As Integer
Dim R As Integer
Dim C As Variant

C = Application.InputBox(prompt:="Starting Column:", Type:=2) '<-- type 2 inidcates a String
R = Application.InputBox(prompt:="Starting Row:", Type:=1)  '<-- type 1 inidcates a Number

Set StartingCell = Range(Cells(R, C), Cells(R, C))

End Function

Sub TestFunc Code (to test the function)

Sub TestFunc()

Dim StartCell As Range
Dim StartCellAddress As String

Set StartCell = StartingCell '<-- set the Range address to a variable (using the function)
StartCellAddress = StartCell.Address '<-- read the Range address to a String

End Sub