Ozgrid Excel Newsletter. Excel Newsletter Archives

NEW! Free Questions, Newsletter ONLY. You need a username and password from the Excel Help forum. If you do not have one, join here for free.

Excel Tips | EXCEL VBA TIPS

Excel Subtotals and making them dynamic

The SUBTOTAL Function in Excel is used to perform a specified function on a range of Auto filtered cells. When the Auto Filter has been applied the SUBTOTAL function will only use the visible cells, all hidden rows are ignored. The operation performed is solely dependent on the number (between 1 and 11) that we supply to it's first argument Function_num For example;

=SUBTOTAL(1,A1:A100)

will Average all visible cell in the range A1:A100 after an Auto Filter has been applied. If all rows in A1:A100 are visible it will simply Average them all and give the same result as

=AVERAGE(A1:A100)

The number for the first SUBTOTAL argument, Function_num, and it's corresponding function are as shown below

Function_Num	Function
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV
8	STDEVP
9	SUM
10	VAR
11	VARP

As we only need to use a number between 1 and 11 we can have the one SUBTOTAL function perform a function of our choice. We can even make the choice from a drop-down list which resides in any cell. Here is how;

Add all the function names, in the same order as above, to a range of cells. I will use D1:D11

With this range selected, click in the Name Box (white box left of the Formula Bar) and type the name: Subs and then click Enter.

Select all of Column D and go to Format>Column>Hide

Go to View>Toolbars>Forms and then click on the Combo box Control and click cell C2

Use the Size Handles to size the combo box so it can display the longest function name, i.e AVERAGE

Right click on the Combo box and choose Format control then the Control tab.

In the Input range: type: Subs In the Cell link: box type:$C$2 Change the Drop down lines: to 11

In cell C3 Enter this formula: =IF($C$2="","","Result of "&INDEX(Subs,$C$2))

In cell C4 Enter this formula: =IF($C$2="","",SUBTOTAL($C$2,$A$4:$A$100)) Where $A$4:$A$100 is the range the SUBTOTAL should act on.

Now all you need to do is select the required SUBTOTAL function from the Combo box and the correct result will be displayed.

Excel VBA Macro Codes

While I would never suggest anyone use Manual calculation in Excel, I realize that many still do. All it means is you have Spreadsheet Design issue that you SHOULD fix rather than cater to.

Excel Function For Calculation Status

 Function CalculationState() As String 
    Application.Volatile 
     
    Select  Case Application.CalculationState 
    Case 0: CalculationState = "Calculating" 
    Case 1: CalculationState = "Done" 
    Case 2: CalculationState = "Pending" 
    End Select 
     
     
End Function

Excel Function For Calculation Mode

Function CalculationMode() As String 
    Dim cMode As XlCalculation 
    Application.Volatile 
    cMode = Application.Calculation 
    Select Case cMode 
    Case xlCalculationAutomatic: CalculationMode = "Auto" 
    Case xlCalculationManual: CalculationMode = "Manual" 
    Case xlCalculationSemiautomatic: CalculationMode = "Semi-Auto" 
    End Select 
     
     
End Function

Use like;
=CalculationState()

and

=CalculationMode()

Both are Volatile Functions and will recalculate when most action is used in Excel. IMPORTANT if you are in Manual Calculation neither will auto recalculate

Suppose you have 2 Columns that has cells with comma separated values in them. E.G;

A1=Cat,dog,1,10,house

B1=Dog,mouse,11,10,home

You need to use 2 more cells and return a comma separated list of the difference. E.G:

C1: =GetDiffs1(A1,B1) which would return "cat,1,house" as neither of these values exist in B1

D1: =GetDiffs2(A1,B1) which would return "mouse,11,home" as neither of these values exist in A1

Now simply use the Fill Handle to copy down

Custom Function 1

Function GetDiffs1(Cell1 As  Range, Cell2 As Range) As String 
    Dim Array1, Array2, lLoop As Long 
    Dim strDiff As String, strDiffs As String 
    Dim lCheck As Long 
     
     
    Array1 =  Split(Replace(Cell1, " ", ""), ",") 
    Array2 = Split(Replace(Cell2, " ", ""), ",") 
    On  Error Resume Next 
    With WorksheetFunction 
        For lLoop = 0 To UBound(Array1) 
            strDiff = vbNullString 
            strDiff = .Index(Array2, 1, .Match(Array1(lLoop), Array2, 0)) 
            If strDiff = vbNullString Then 
                lCheck = 0 
                lCheck = .Match(Array1(lLoop), Array2, 0) 
                 
                If lCheck = 0 Then 
                    strDiffs = strDiffs & "," & Array1(lLoop) 
                End If 
            End If 
             
        Next lLoop 
    End With 
     
    GetDiffs1 = Trim(Right(strDiffs, Len(strDiffs) - 1)) 
End Function

Custom Function 2

Function GetDiffs2(Cell1 As Range, Cell2 As Range) As String 
    Dim Array1, Array2, lLoop As Long 
    Dim strDiff As String, strDiffs As String 
    Dim lCheck As Long 
     
     
    Array1 = Split(Replace(Cell1, " ", ""), ",") 
    Array2 = Split(Replace(Cell2, " ", ""), ",") 
    On Error Resume Next 
    With WorksheetFunction 
        For lLoop = 0 To UBound(Array2) 
            strDiff = vbNullString 
            strDiff = .Index(Array1, 1, .Match(Array2(lLoop), Array1, 0)) 
            If strDiff = vbNullString Then 
                lCheck = 0 
                lCheck = .Match(Array2(lLoop), Array1, 0) 
                 
                If lCheck = 0 Then 
                    strDiffs = strDiffs & "," & Array2(lLoop) 
                End If 
            End If 
             
        Next lLoop 
    End With 
     
    GetDiffs2 = Trim(Right(strDiffs, Len(strDiffs) - 1)) 
End Function

Extract Differences Between 2 Comma Separated Range Arrays Macro

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft