how to get sheets("sname").select to work?

[dleizer]

Folks

I am new to VBA and I am learning as I go along mainly thanks to your generosity (I posted two queries in the last couple of weeks and got instant and practical response to both. Thanks!) and Mr Google’s functionality

The question today if I may relates to the way sheets(“sheetname”).select work or in my case doesn’t work in user defined function (It works fine in a Sub macro)


I have a workbook with 3 sheets called summary, data and Test
My function is as follows :

Function test()
Sheets("Summary").Select
NM1 = ActiveSheet.Name
‘ do some work
Sheets("Data").Select 'Navigate to data
NM2 = ActiveSheet.Name
‘ do some work
End function

When I starti am positioned on the test work sheet

When I convert this function to a sub and run the macro I can see that it work ie nm1 & nm2 show “summary” and “Data” respectively but when I executed it as a function I find that nm1 and nm2 are both set to “test”

Why is it so

 

[Andrew Poulsom]

A function can only return a value. It can't perform actions (generally speaking).

 

[dleizer]

Andrew

Thanks for your prompt response and an apology for not making my query clearer for what I really wish to do is return a value from the test() udf back to the cell where is called from ie. at the end of "the do some work" comment i was going to include a statement like test = result of that work

Let me have another go at it

What I am trying to do is something like this

I have two sheets data and summary

Data contains 1000s of records (raws) which are then “summarized” in the summary sheet

The summary sheet has an entry for each year

The way the summarizing is derived is by examining each one of the records in the data sheet and deciding if it meet a certain criteria which is in turn dependent on data in the preceding or subsequent record

My intention was to write a UDF which first open the Summary sheet to get a parameter from the summary sheet and them move to the data sheet and starting at the top read each record sequentially to figure out if data from that record should be included in the calculation

Since the data is sorted I would like to think that I could use a “find/locate” type function to locate the first instance which meet my criteria and then process the next x records which meet that same criteria

I think I can write the logic which locate and read/process each record in the data sheet but what I don’t know is how to shift from summary to data and then back to summary

I suspect that there are other ways to achieve this without complex vba code but I could not think of any and I also thought that it would be nice to learn more about vba programing

Is what I am trying to do is achievable using my approach and if not can you recommend another why of so doing ?

Is there a place I can look which explain which vba commands can be used in Functions and which one can only be used in Macros (using the sheets(“xxx”).select command did not give any error it just did not work??

Thanks


Here is a simple
The

 

[GTO]

A function can only return a value. It can't perform actions (generally speaking).

...My intention was to write a UDF which first open the Summary sheet to...

Hi there,

Only to add clarity, a User Defined Function cannot perform any meaningful actions. Thus, the Open part, while the UDF may not stop you, it also will not actually happen...

To see this, put 2 workbooks in the same folder - named 'CallingWB.xls' and 'CalledWB.xls'.

To 'CallingWB.xls', add, in a Standard Module:

Option Explicit
    
Sub GetMyVal()
    Debug.Print ThisWorkbook.Path & "\CalledWB.xls"
    ActiveSheet.Cells(1).Value = GetVal(ThisWorkbook.Path & "\CalledWB.xls")
End Sub
        
Function GetVal(FFName As String) As Variant
Dim File2Open As Workbook
    
    Set File2Open = Workbooks.Open(FFName)
    GetVal = File2Open.Worksheets(1).Cells(1)
    File2Open.Close False
End Function

You will see that GetMyVal will run, and return what is in Cells(1). But if you add the function to a sheet as a UDF, nothing will happen.

Hope that helps,

Mark

 

[pgc01]

Hi dleizer

As you have been already told you cannot open the summary sheet or perform any active action with a udf.

Although new to vba you surely have experience in working with the excel functions. As you know there are more that 300 functions that come with excel and you have probably used 50 or 100 or more already. If you remember none of the functions has selected a sheet, or delete a row or a columns, etc. The excel functions simply return a value, do not perform any action that changes the environment.

Your udf will be just another excel function. Like all the others will also not change anything in the environment, will just return a value.

Like Mark says, you can use code to do it in vba mode

HTH.

 

[dleizer]

Folks

Thanks for your collective comments

The message coming back is nice and clear i.e i cant use the UDF to do what i am trying to do

I assume I will be able to figure out how to do what i wish to do by using a sub macro and then run it manually every time i need to get current summary

on reflection i think i may even get away with using a pivot table provided i add some extra columns into the spreadsheet and use some smarts to populate them

Are there any other way to skin such a beast?

On a different note

Can i assume that this reply (i replied to PGC01 (HTH) comments since it was the last to arrive) will be forwarded to each one of you who took the time and trouble to respond to my query?? PGC01 i may need your feedback on this since obviously if my assumption is wrong Mark and Andrew will not be in the loop and i greatly like to thank them as well

 

[GTO]

...Can i assume that this reply (i replied to PGC01 (HTH) comments since it was the last to arrive) will be forwarded to each one of you who took the time and trouble to respond to my query?? PGC01 i may need your feedback on this since obviously if my assumption is wrong Mark and Andrew will not be in the loop and i greatly like to thank them as well

Greetings dleizer,

I have never thought about it, but indeed, 'Reply' could easily be misunderstood. The Reply is actually a reply to the thread if-you-will.

At least at this forum, anyone can view the complete thread, so no worries there.

Mark

 

[dleizer]

thanks and good night (i assume you are in the us. i am on the other side of the world in a small place called Australia)

 

[GTO]

A good night to you as well. Indeed Arizona is one of 'these several united states', in the SouthWestern portion of the Union.

 

[Andrew Poulsom]

You don't need to select/activate objects to use their properties/methods. But some of the methods will not work in a UDF called from a worksheet. Can't you use Excel's built-in functions to perform your calculation?