VBA SelectedSheets not working properly

[DanielCol]

Hello,
Why is it that this function always return 1 when used as a Excel function ?

Function NbFeuillesActives()
  NbFeuillesActives = ActiveWindow.SelectedSheets.Count
End Function

Regards.

 

[mikerickson]

Selecting a sheet or adding to the selected sheets does not trigger calculation.
The formula won't be recalculated when the user selects sheets.

You could make it a volatile function. That wouldn't necessarily be accurate, because the recalculation has to wait until a cell's value is changed.

 

[DanielCol]

Thanks for answering. Unless I miss something, even if I change a cell value, the formula value is still 1 ?

 

[MARK858]

Did you make the Function volatile as Mike stated?

 

[DanielCol]

Yes, I did it. Same result... The funny thing if I execute th code line by line, the result is correct

 

[MARK858]

Please post the code as you currently have it

 

[DanielCol]

Well, after mikerickson suggestion :

Function NbFeuillesActives()
  Application.Volatile
  NbFeuillesActives = ActiveWindow.SelectedSheets.Count
End Function

 

[MARK858]

I'll test once I get back in, about a hour

 

[MARK858]

Code is working fine for me. A4 has the formula and A1 is the cell that I am changing. Is your calculation set to Automatic?

 

[DanielCol]

Weird. Calculation set to automatic, changing any cell value, formula in E3 :

Local problem ? Other users here have the same problem.