UDF function

[NoRomancelnChin]

I think this will be easier to understand if I just set up a scenario for you from scratch. Open a blank sheet. In cells B2:D10 enter random digits between 1 and 10. In B1 type in =SUBTOTAL(9,B2:B10) which should give you the sum of that column. Use autocomplete to fill in columns C and D with the corresponding formula.

I want a function to go in column A that will take the average of each row WHICH takes into account hidden columns. When I type in =SUBTOTAL(101,B1:D1) in cell A1, it returns a divide by 0 error. I understand it's because I'm taking a subtotal of a subtotal and that can't work. The formula works for the cells underneath, because rows 2 to 10 are filled with raw data. However, it doens't take into account hidden columns. So far, I've written a macro that finds the average and takes into account hidden rows. This is the code I've written, and I assigned it to a shortcut key.........................

Please Login or Register  to view this content.

......................
Is there a way to turn this into a user function such that I can type in the function name and a reference and it will find the average of the reference taking into account hidden columns? I don't know how to do this. Any help would be greatly appreciated. Thanks.

[avveerkar]

I think this will be easier to understand if I just set up a scenario for you from scratch. Open a blank sheet. In cells B2:D10 enter random digits between 1 and 10. In B1 type in =SUBTOTAL(9,B2:B10) which should give you the sum of that column. Use autocomplete to fill in columns C and D with the corresponding formula.

I want a function to go in column A that will take the average of each row WHICH takes into account hidden columns. When I type in =SUBTOTAL(101,B1:D1) in cell A1, it returns a divide by 0 error. I understand it's because I'm taking a subtotal of a subtotal and that can't work. The formula works for the cells underneath, because rows 2 to 10 are filled with raw data. However, it doens't take into account hidden columns. So far, I've written a macro that finds the average and takes into account hidden rows. This is the code I've written, and I assigned it to a shortcut key.........................



Sub AverageVisible()
Dim Rng As Range
Dim c As Range
MyRow = ActiveCell.Row

Set Rng = Range("H" & MyRow & ":CO" & MyRow).SpecialCells(xlCellTypeVisible)
For Each c In Rng
MySum = MySum + c.Value
Next c
For Each c In Rng
MyCount = MyCount + 1
Next c
Range("G" & MyRow).Value = MySum / MyCount
End Sub





......................
Is there a way to turn this into a user function such that I can type in the function name and a reference and it will find the average of the reference taking into account hidden columns? I don't know how to do this. Any help would be greatly appreciated. Thanks.

Please Login or Register  to view this content.

I have only converted the sub to function. You will have to copy this to standard module of the workbook and it will be available only to this workbook.
This function is "hardwired". In the sense it will always find out sum of cols H to CO in Active Cell row. Better way may be to have an option to give range as input to the function for it to claculate the average.

A V Veerkar

[NoRomancelnChin]

Yeah I have no idea how to do that. I don't want to give the impression that people have to stick to my code here. If someone can think of a better, new way to get my task done, I'm definitely open to it.

[avveerkar]

Sorry I gave you a function using your own sub but when I tested it I found that it does not work. SpecialCells(xlCellTypeVisible) somehow does not work when used in function ( does not account for hidden rows ) but when used in sub seems to work. It has foxed me.

I have written a sub and a function which are identical and expected to give same results. These are

Please Login or Register  to view this content.

Without any columns hidden both give me a count of 10. But if I hide two columns, say D and E, sub gives me 8 which is expected but function gives me 10. This has foxed me totally. I am sure someone can explain this.

As far as your problem goes, I think what you have done is correct. If hidden columns were not the issue I would suggest that instead of subtotalling the rows you could sum them and then take subtotal of the sums. You will not get divide by zero error.

A V Veerkar

[NoRomancelnChin]

That is weird that the sub would count 8 yet the function still counts ten. Maybe this could get somewhere: how can I write a function that would, say, be equal to 2x the cell to the right? My ultimate goal is to be able to paste this 'visible average' function into about 800 rows of data, so the function has to be row specific to the row it is in.

[NoRomancelnChin]

Actually, I just figured out how to do that and with that small gain of experience I wrote this.

Please Login or Register  to view this content.

But like you said, Function is not taking into account hidden columns. This function works though in taking the average. It's just the hidden column aspect that is failing.

[avveerkar]

Can someone help? In my post I have shown a sub and a function to count visible columns. The sub works but the function does not. We would like to know where we are going wrong. Or is it quirk in Excel?

A V Veerkar

[christophocles]

Can someone help? In my post I have shown a sub and a function to count visible columns. The sub works but the function does not. We would like to know where we are going wrong. Or is it quirk in Excel?

A V Veerkar

I am having the exact same problem. I discovered the subtotal function works well with Autofiltered data but there are only a few functions available for it. I want to create a function that will calculate the 1st and 3rd QUARTILE of the visible autofiltered data. So far, I have not been able to get the SpecialCells method to work at all inside of a user-defined function.

Does anyone here know of a workaround for this problem? Has a bug report been filed on MSDN? I know UDFs are picky about what you can do with them, but I can't understand why SpecialCells should not work.

[NoRomancelnChin]

Bump......

[MrShorty]

I don't know why the special cells method didn't work.

I worked it out by testing the height property of each cell (My test data were layed out in a column, and I hid rows). Hidden cells have a height of 0, so I simply test for height > 1

Please Login or Register  to view this content.

If you're going to be hiding columns instead of rows, then test against the width property. If it could go either way, then you'll have to test both.

[avveerkar]

Actually, I just figured out how to do that and with that small gain of experience I wrote this.

Please Login or Register  to view this content.

But like you said, Function is not taking into account hidden columns. This function works though in taking the average. It's just the hidden column aspect that is failing.

Thank you Mr. Shorty. That is a good work-around.

NoRomancelnChin, now we can modify your function as

Please Login or Register  to view this content.

This seems to work. UDF is sort of static, in the sense that it will not automatically recalculate if a value of the cell on which it works changes. You have to sort of re-enter the data in the cell in which you have used the UDF. In third line I have used "Application.Volatile" which takes care of this.

A V Veerkar