Thread: VB module changes values on all sheets every time it runs

I have the following module:

Option Explicit

Function ShtCnt() As Long
    Application.Volatile
    ShtCnt = ActiveWorkbook.Sheets.Count
End Function

this is designed to give me a total count of worksheets in a workbook.

The problem is that I have multiple workbooks using this command, and I've realized now that when I use it on one workbook, it takes those values and places them in ALL workbooks, which is causing inaccurate results to be displayed everywhere it's used EXCEPT whatever sheet I'm currently working on (as it does the formula for that sheet)

for example:
I create workbook 1. when done, it has 5 worksheets total. I put in the formula where I want it and it displays (accurately) "5"

I then create workbook 2 while workbook 1 is still open. Workbook 2 has 3 sheets. When done creating and filling it with info, I put the formula where I want it in workbook 2. It calculates "3".

the issue, is it takes that "3" and plugs it into both workbook 1 and workbook 2's cells that contain the shtcnt() function.

The end result is worksheet 2 has the correct information, but workbook 1 now displays "3" instead of "5"

How would i stop it from doing this? The only Idea I have is to create a "shtcnt1()" "shtcnt2" etc etc for every worksheet I create, but that's not very practical

aff219;
Your function uses "ActiveWorkbook". There is only 1 ActiveWorkbook at a time. So ALL open workbooks will get the exact same answer from that function at any time. If you go back to workbook1 and cause it to calculate, you will then see that workbook 2 has 5.

You need to change your function to :

Function ShtCnt(ByVal r as Range) as Long
    Dim wb as Workbook
    Set wb = r.Worksheet.Parent
    ShtCnt = wb.Sheets.Count
End Function

Then in your worksheet enter "=ShtCnt(A1)" (any cell address will do).

Originally Posted by foxguy

aff219;
Your function uses "ActiveWorkbook". There is only 1 ActiveWorkbook at a time. So ALL open workbooks will get the exact same answer from that function at any time. If you go back to workbook1 and cause it to calculate, you will then see that workbook 2 has 5.

You need to change your function to :

Function ShtCnt(ByVal r as Range) as Long
    Dim wb as Workbook
    Set wb = r.Worksheet.Parent
    ShtCnt = wb.Sheets.Count
End Function

Then in your worksheet enter "=ShtCnt(A1)" (any cell address will do).

thanks, might i ask what the A1 part does?

*edit*

when i use it I seem to get #VALUE! error

The A1 is just any cell address. You could use Z69 or anything else.
I don't know how to pass a workbook to the Function. So I pass a range (any cell) and let the Function figure out what workbook it is in.

It works fine on my computer.
Do you know how to debug a macro?

If you don't, make a new workbook and put the function in it and try to use it in any worksheet. Make sure the error occurs then upload the workbook and I'll take a look.

If you put the code into a Standard module instead of a Sheet module it will be available to all worksheets. Code in a Sheet module is only available to that theet.