Select cells from function in VBA

Question

I want to have a macro that first activates a particular worksheet and then selects a particular cell. However, when I run the code below I get the following message:

Run-time error:'1004': Select method of Range class failed.

What am I doing wrong?

Function selectingCells(myWs As String, myCell As Range)

    Sheets(myWs).Activate
    myCell.Select

End Function

Sub callingFunction()

    Call selectingCells("Data", Range("A1"))
    Call selectingCells("Picklist", Range("A1"))

End Sub

You should try to avoid using select. Take a look at this: [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — RSinohara, May 22 '15 at 13:41

FreeMan · Accepted Answer · 2015-05-22T13:41:38.810

Range("A1") applies to a specific cell on the currently active worksheet. Your error is because you're trying to select a cell on whatever sheet was active when you defined the range (i.e. made the function call) while you're on a different worksheet.

This will work for you:

Function selectingCells(myWs As String, myCell As String)
    Sheets(myWs).Activate
    Range(myCell).Select
End Function

Sub callingFunction()
    Call selectingCells("Data", "A1")
    Call selectingCells("Picklist", "A1")
End Sub

A couple of notes:

Unless this is a smallest case reproducible bit of code, there's no need for selectingCells to be a Function since it doesn't return anything. You'd be better served by making it a Sub
There's no need for Call in callingFunction. Call is deprecated and only maintained for backward compatibility

Again, unless there's more to your code than this sample, callingFunction could be rewritten as:

sub callingFunction()
  Sheets("Data").Range("A1").select
  Sheets("Picklist").Range("A1").select
end sub

and be much more efficient

You're welcome. Also, @Victor, please take a look at the [link](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) recommended by [RSinohara](http://stackoverflow.com/questions/30398010/select-cells-from-function-in-vba/30398190#comment48884234_30398010). There are _very_ few occasions when using `.select` is necessary, and that link will show you how to avoid using it and the confusion you just ran into. — FreeMan, May 22 '15 at 13:54

score 3 · Answer 2 · answered May 22 '15 at 13:37

The myCell parameter is a range reference which exists on some other worksheet. Try passing in the string value of cell address you want selected, like this:

Function selectingCells(myWs As String, myCell As String)

Sheets(myWs).Activate
Sheets(myWs).Range(myCell).Select

End Function

Sub callingFunction()

Call selectingCells("Data", "A1")
Call selectingCells("Picklist", "A1")

End Sub

Is there some reason why selectingCells is defined as a function - will it return some value to the calling code? If not, you can define it as a procedure.

Jinx! that looks really familiar... :D – FreeMan May 22 '15 at 13:39 — FreeMan, May 22 '15 at 13:39

score 1 · Answer 3 · answered May 22 '15 at 13:38

In the Sub, you are defining Ranges for the ActiveSheet. So if you select other sheet and try to select the ranges, it's not working.
To correct this issue, define the ranges properly :

Sub callingFunction()

    Call selectingCells("Data", ThisWorkbook.Sheets("Data").Range("A1"))
    Call selectingCells("Picklist", ThisWorkbook.Sheets("Picklist").Range("A1"))

End Sub

score 1 · Answer 4 · answered May 22 '15 at 13:39

Because you don't qualify your range object your functions act like this:

Call selectingCells("Data", Activesheet.Range("A1"))
Call selectingCells("Picklist", Activesheet.Range("A1"))

If you are not on the sheet that's the same as the range you're trying to select you get the error you're getting. You change your function to one parameter if you'd like since the worksheet information is included in it. Something like this:

Function selectingCells(myCell As Range)

    myCell.parent.Activate
    myCell.Select

End Function

Sub callingFunction()

    Call selectingCells(sheets("Data").Range("A1"))
    Call selectingCells(sheets("Picklist").Range("A1"))

End Sub

score 0 · Answer 5 · answered May 22 '15 at 13:40

0

try:

Function selectingCells(myWs As String, myCell As Range)

Set mysheet = Sheets(myWs)
mysheet.Activate
Set mySelectedCell = mysheet.Range(myCell.Address)
mysheet.Activate
mySelectedCell.Select
End Function

answered May 22 '15 at 13:40

Federico Sanchez

145
1
2
12

score 0 · Answer 6 · answered May 22 '15 at 13:42

0

why you use function for such simple task?

use this method

Sub test()
    Sheets("Data").[A1].Select
    Sheets("Picklist").[A1].Select
End sub

answered May 22 '15 at 13:42

Vasily

5,707
3
19
34

Select cells from function in VBA

6 Answers6