Excel does not recognize worksheetfunction.subtotal

[BrianPagan]

Hello!

I have many subtotals in one column and one of them in the middle is a vba formula worksheetfunction.subtotal, but excel does not know that and behaves as if it were just a number there and sums up everything. How could I make excel recognize vba subtotal?

If that is not possible, then I would like to give excel subtotal formula a range that I have defined in vba. How could I do that?

thanks!

 

[RoryA]

Could you clarify exactly what you have in the cell - i.e. how you are calling the worksheetfunction.subtotal?

 

[BrianPagan]

I copied my function code here and it works. Just that I would like excel to know that it is subtotal.

In the cell I enter =summa_tegevus()

Function summa_tegevus()

Dim m As Integer
Dim allcells As Range

Application.Volatile
If TypeName(Application.Caller) = "Range" Then
If Application.Caller.Row <> 65536 And Application.Caller.Column <> 1 Then

m = 1

Do Until Range(Application.Caller.Offset(m, -2).Address) = ""
m = m + 1
Loop

Set allcells = Range(Application.Caller.Offset(1, 0).Address, Application.Caller.Offset(m, 0).Address)
summa_tegevus = Application.WorksheetFunction.Subtotal(9, allcells)

End If
End If

End Function

 

[RoryA]

Oh, I see what you mean - you want the SUBTOTAL function to ignore your custom function? No way to do that that I know of - it will only ignore actual SUBTOTAL formulas.

 

[BrianPagan]

But is there a way to giva excel formula a vba defined range?
for example: SUBTOTAL(9;allcells) where allcells is defined in vba function

 

[RoryA]

You can create a function that returns a range and then use that with this sort of syntax:
=SUBTOTAL(9;GetCells())
for example.

 

[BrianPagan]

=SUBTOTAL(9;GetCells(A1))

A1 =function_name that returns a range addresses. Is that what you mean?

I tried that but didnt work. I tried to find information on GetCells() in the internet, couldnt really find anything useful. Couldnt find it in excel's list of formulas either.
Could you tell me a bit more about GetCells()?

Thank You

 

[RoryA]

GetCells would be your new function that returns a range. For example:

Function GetCells() as range

Dim m As Integer
Dim allcells As Range

Application.Volatile
If TypeName(Application.Caller) = "Range" Then
If Application.Caller.Row <> 65536 And Application.Caller.Column <> 1 Then

m = 1

Do Until Range(Application.Caller.Offset(m, -2).Address) = ""
m = m + 1
Loop

Set GetCells = Application.caller.worksheet.Range(Application.Caller.Offset(1, 0).Address, Application.Caller.Offset(m, 0).Address)

End If
End If

End Function

and you then use the formula syntax I posted.

 

[RoryA]

FYI, I think you could do this without any code.

 

[BrianPagan]

It worked perfectly. Thank you very much!