Selection not working

[vbastruggle]

I have a simple public function within a module. I am planning to execute the function as a user defined function within a worksheet. For now, I only need to select a cell and get its content. This is not working correctly and I hope someone can enlighten me. The simple code from the function is below. If I execute the code from the VBA editor it works fine and I can see that it does, in fact, select Cell D6 on the Test worksheet. However, if I set up a cell with my user defined formula, the code seems to only select from the cell that was previously selected in the worksheet. Can someone please explain why it would work differently when executed in different ways and what I need to do to get the user defined formula to work.

Sheets("Test").Range("D6").Select
MsgBox Selection.Value

 

[Joe4]

Welcome to the Board!

Functions are not designed to do things like "select" cells. They are usually designed to perform some calculation are return a value. So you should not ever see the word "Select" inside a Function.
Procedures, on the other hand, often update cells and update your sheets. So you typically see "Select" statements there.

Also note, in order to return a value from a specified cell, there is no need to actually select it, i.e.

MsgBox [COLOR=#333333]Sheets("Test").Range("D6").Value[/COLOR]

 

[Jan Mach]

1. The normal way of programming is to use named ranges. Name the cell, say, TestCell.
2. Why do you use the Value property, type of which is ugly and slow Variant, when you need String for MsgBox?

Hence
MsgBox [TextCell].Text


By the way, you do NOT need selections for updating cells od sheets in a procedure! You can simply write

[TextCell] = ... some value ...​

 

[Joe4]

1. The normal way of programming is to use named ranges. Name the cell, say, TestCell.

That certainly is ONE way, but I don't know that it qualifies as the "normal" way ("normal" according to who?).
For a single cell, it may not be worth the effort to name it.

 

[RoryA]

The normal way of programming is of course to avoid using .Text unless absolutely necessary as it is considerably slower than .Value and gets worse the more you use it.

 

[vbastruggle]

Welcome to the Board!

Functions are not designed to do things like "select" cells. They are usually designed to perform some calculation are return a value. So you should not ever see the word "Select" inside a Function.
Procedures, on the other hand, often update cells and update your sheets. So you typically see "Select" statements there.

Also note, in order to return a value from a specified cell, there is no need to actually select it, i.e.

MsgBox [COLOR=#333333]Sheets("Test").Range("D6").Value[/COLOR]

Thanks for your reply. I have moved my code to a sub routine now but still have the same problem. Your code for the msgbox does work but that isn't really what I need. I am trying to select a column of data. Below is how I originally planned to do it. First activate the sheet with the data, then select the column of data starting at a given cell. When I step through the code in the editor, I can see that it does the selection correctly. When I run it outside of the editor, it does not select anything. I have used this for other projects before and it has always worked. Not sure if it is the newer version of excel or of I have a setting somewhere that is causing the problem.

Sheets("Test Execution").Activate
Sheets("Test Execution").Range("D6").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select

 

[Joe4]

It shouldn't have anything to do with the version of Excel that you are on.

Can you post your entire procedure?
What is the name of the module you are placing the procedure in?

 

[vbastruggle]

Here is the procedure. It is in a module named module1. If I just execute it from within the editor, it works fine and the msgbox displays the correct values. If I execute the procedure from the worksheet, the selections don't work and it seems to only be aware of the cell that was active when the procedure was executed.

Public Sub test()
Dim d

Sheets("Test Execution").Activate
ActiveSheet.Range("D6").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select

For Each d In Selection
MsgBox "Value: " & d.Value
Next d
End Sub

 

[Joe4]

The code seems to work just fine for me.
You may want to try shutting Excel down and re-starting.

Also, check for the following:
- is your sheet hidden?
- does your sheet have protected cells?
- does your sheet have merged cells?
- how exactly are you running the code from your sheet?

You may want to confirm that how you are running the code is actually looking at the right thing and working. That is easy to do by adding a MsgBox, i.e.

Public Sub test()
Dim d

MsgBox "Code is about to run!"

Sheets("Test Execution").Activate
ActiveSheet.Range("D6").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select

For Each d In Selection
MsgBox "Value: " & d.Value
Next d
End Sub

Try running your code from the sheet. If you are not seeing that MsgBox at the beginning, then the issue is probably with how you are trying to call your code.

 

[vbastruggle]

I have restarted Excel and now it is working for me also. Should have thought of that to start with. Thanks so much for the help.