Excel 2010 – Shown Developer tab in the Ribbon

Excel 2010

File->Option->Customize Ribbon->Click Developer on the Customize the Ribbon, Main Tabs List

© Copyright Exceltipsandkeys All Rights Reserved.

Excel File – Child Monthly Expense w Download file

This Child Monthly Expense spreadsheet is designed for parents to create a budget for the kid and track the actual expense.

It is very easy to use.

Parents can use it to create a budget for an entire year.

Parents can use it to track every expense on the kid.

If you are expectant parents, this spreadsheet can help you to estimate how much you will expect to spend for you new family member.

The spreadsheet contains a monthly summary report.

The spreadsheet tells what the difference is between the actual expenses and the estimate ones.

Hopefully, doing this can help you make predictions about your future finances.

Here are two versions. Feel free to pick one you like.

The Protected version prevents the accidental erasure of formulas. It may be good to a novice Excel user.

I wish you like it.

Any feedback is appreciated.

Thank you.

© Copyright Exceltipsandkeys All Rights Reserved.

Excel 2010 – Sparkline

Recently, I updated Excel to 2010 version. I noticed that under “Insert Ribbon”, there is a new charting feature called SPARKINE. It allows the user to generate a micro-chart which could show trend information.

 
I really feel it help visualize tabular data without taking too much space.

For example,

We would like to see how the sale prices of the fruits fluctuate on the monthly basis. The sparklines are shown in the column I.

Here are the steps to make sparkline.

Step 1, Go to Insert->Sparkline and Select the type of the sparkline (here, line is used)

Step 2, The Data range is the range of data you would like to make the sparkline.

           Location Range is the cell you would like to shown the sparkline.

Design Sparkline.

If you click on the cell with the sparkline in it, the “Sparkline Tools” is shown on the top of the Excel window. If you click “Sparkline Tools”, a new ribbon called “Sparkline Tools Design” is shown. This is the place where all the sparkline formatting options are included.

Like Formula, Sparkline can be auto-filled. It can also be copied and pasted.

Unlikely, when the file with sparkline is opened in Excel 2007, sparklines are not shown.

© Copyright Exceltipsandkeys All Rights Reserved.

Excel VBA – Use SHELL function to open a folder

I try to make my administrative work fun.

I got a project that I need group more than 40 files together in one big folder. Those 40 files are saved in the different folder but the good thing is that they have been saved based on the same pattern.

Instead of opening folders one by one, I use the shell function.

Shell (pathname [,windowstyle])

Open a folder:

Shell "Explorer.exe " & "Filepath" & foldername, vbNormalFocus

Or Shell "Explorer.exe " & "Filepath" & foldername, 1

Note: The blank behind the Explorer.exe is important.

For example,

Shell "Explorer.exe C:\Development\", vbNormalFocus

Or

Shell "Explorer.exe " & "C:\Development\", 1

The above code opens a folder named Development in the C: Drive.

Open a group of folders:

For example, There are several files in the Development Folder named as 1, 2, 3,…

Sub OpenFolders()

    Dim i As Integer

    For i = 1 To 20

    Shell "Explorer.exe " & "C:\Development\" & CStr(i) & "\", vbNormalFocus

    Next i

End Sub

The above code enables to open 20 folders at the same time.

© Copyright Exceltipsandkeys All Rights Reserved.

Excel VBA –ReDim Preserve

ReDim statement is used to resize a dynamic array. When ReDim Statement is used, all the values assigned prior to resize the array are erased.

By adding the Preserve keyword after the ReDim, the data in the array prior to resize can be kept.

We copy and paste the following macro into Excel. The Output of message box is 2. After using ReDim Preserve statement to resize the array, the Output of message box is 2. The value in the array is preserved. Then, after using ReDim statement to resize the array, the Output of message box is blank. The value in the array is erased.

 Sub test()

    Dim PmtStream() As Variant

    TotalCount = 10

    '-----ReDim PmtStream to the size which is needed.

    ReDim PmtStream(1 To TotalCount)

    '----we read the date to PmtStream.

    For i = 1 To 10

    PmtStream(i) = i * 2

    Next i    

     MsgBox PmtStream(1)

     ReDim Preserve PmtStream(1 To 20)

     MsgBox PmtStream(1)

     '-----After ReDim Preserve comment, the output is the same as the previous one

     ReDim PmtStream(1 To 10)

     MsgBox PmtStream(1)

     '-----After ReDim comment, the output is blank.

End Sub

 However, unlike ReDim statement, ReDim Preserve can only resize the last array dimension and the number of the dimensions cannot be changed.

For example,

Sub test()

    Dim PmtStream() As Integer

     ReDim Preserve PmtStream(1 To 10, 1 To 4)   ‘OK

     ReDim PmtStream(1 To 15, 1 To 10, 1 To 20)  ‘OK

     ReDim PmtStream(1 To 10, 1 To 10)           ‘OK

ReDim Preserve PmtStream(1 To 10, 1 To 4)   ‘OK

     ReDim Preserve PmtStream(1 To 15, 1 To 4)   ‘Run Time error

ReDim Preserve PmtStream(1 To 15)           ‘Run Time error

End Sub

© Copyright Exceltipsandkeys All Rights Reserved.

Excel VBA – Copy Filtered data using Autofilter in VBA into Array (2) - with no blank data

The macro in the “Copy Filtered data using Autofilter in VBA into Array” transfers the filtered data using autofilter comment to an array. The imperfect thing about it is that the transferred array contains the blank data in it because the size of the array equals to the size of the whole filtered range.

The easier way to solve the issue is to copy the non blank data into the new array.

Sub main()

    Dim FiltResult() As Variant

    Dim Filtersheet As Worksheet

    Dim FiltRange As Range

    Dim FiltField As Integer

    Dim FiltCrit As Variant

    Set Filtersheet = sheets("PStream")

    Set FiltRange = Filtersheet.Range("C6:F7352")

    FiltField = 1

    FiltCrit = 900

    FiltResult = Copyfilter(Filtersheet, FiltRange, FiltField, FiltCrit)

    '''''

    ''''Codes related with the filtered result

    ''''

End Sub

Function Copyfilter(Sht As Worksheet, Rng As Range, FField As Integer, Crit As Variant)

         Dim Arr() As Variant

         Dim ArrNew() As Variant

         Sht.Select

         With Sht

           Rng.AutoFilter

           RowCount = Rng.Rows.Count

           ColCount = Rng.Columns.Count

           Count = 1

           ReDim Arr(1 To RowCount, 1 To ColCount)

           Rng.AutoFilter Field:=FField, Criteria1:=Crit

            '------Copy the data to array for future use

            '----Do not include the table tilte

           For i = 2 To RowCount

                If Rng(i, 1).Rows.Hidden = False Then

                   For j = 1 To ColCount

                   Arr(Count, j) = Rng(i, j).Value

                   Next j

                   Count = Count + 1

                 End If

            Next i

           Rng.AutoFilter

        End With

        '----Copy the nonblank data into a new array

   ReDim ArrNew(1 To Count - 1, 1 To ColCount)

        For i = 1 To Count - 1

            For j = 1 To ColCount

             ArrNew(i, j) = Arr(i, j)

            Next j

        Next i

        '----Transfer the data to the main sub

         Copyfilter = ArrNew

End Function

Another way is to using ReDim Preserve Statement. The trick part is that when using Preserve, only the upper bound of the array can be changed. Otherwise the run time error is shown. I transpose the array to use the ReDim Preserve Statement to eliminated the un need elements.

Sub main2()

    Dim FiltResult() As Variant

    Dim Filtersheet As Worksheet

    Dim FiltRange As Range

    Dim FiltField As Integer

    Dim FiltCrit As Variant

    Set Filtersheet = sheets("PStream")

    Set FiltRange = Filtersheet.Range("C6:F7352")

    FiltField = 1

    FiltCrit = 9002

    FiltResult = Copyfilter(Filtersheet, FiltRange, FiltField, FiltCrit)

    '''''

    ''''Codes related with the filtered result

    ''''Filter result is transposed array

    ''''

End Sub

Function Copyfilter(Sht As Worksheet, Rng As Range, FField As Integer, Crit As Variant)

         Dim Arr() As Variant

           Sht.Select

         With Sht

           Rng.AutoFilter

           RowCount = Rng.Rows.Count

           ColCount = Rng.Columns.Count

           Count = 1

           ReDim Arr(1 To ColCount, 1 To RowCount)

           Rng.AutoFilter Field:=FField, Criteria1:=Crit

            '------Copy the data to array for future use

            '------Transpose the array, the column to row and row to column

           For i = 2 To RowCount

                If Rng(i, 1).Rows.Hidden = False Then

                   For j = 1 To ColCount

                   Arr(j, Count) = Rng(i, j).Value

                   Next j

                   Count = Count + 1

                 End If

            Next i

           Rng.AutoFilter

        End With

        'Redim the size of array, only keep the nonblank data

         ReDim Preserve Arr(1 To ColCount, 1 To Count - 1)

        '----Transfer the data to the main sub

        Copyfilter = Arr

End Function

© Copyright Exceltipsandkeys All Rights Reserved.

Excel VBA – Remove the autofilter in Excel VBA

Auto filter is a very useful comment to extract the data which satisfy the required criteria. When we program for other users, it is good to remove the applied autofilter to the range to eliminate the confusion.

Function Copyfilter(Sht As Worksheet, Rng As Range, FField As Integer, Crit As Variant)

Dim Rngfilter As Range

Sht.Select

With Sht

Rng.AutoFilter Field:=FField, Criteria1:=Crit

'----The following comments can test whether the auto filter or filter mode is active.

If .FilterMode = True Then MsgBox "Filter is used" Else MsgBox "Filter is not used"

If .AutoFilterMode = True Then MsgBox "Filter is visible" Else MsgBox "Filter is not visible"

'-----The following can remove the autofilter

If .FilterMode = True Then .ShowAllData     

End With

End Function

However, using the .ShowAllData sometimes gives the error message. For example, there are no data meeting the criteria.

The error message comes.

One way to solve the problem is to add one comment before .ShowAllData.

On Error Resume Next

If .FilterMode = True Then .ShowAllData

The error message would not show and the code can continue execute.  However, data in the filtered range still hide.

The better way to do it is to use the following comment.

Rng.AutoFilter

It removes the previous autofilter applications in the select range.

Another case is that we want to detect the presence of any active autofilter and remove them.

If ActiveSheet.AutoFilterMode = True Then ActiveSheet.Range("A1").AutoFilter

This comment sometimes gives the error. It is better to combine with error handling code

On Error Resume Next

If ActiveSheet.AutoFilterMode = True Then ActiveSheet.Range("A1").AutoFilter

If Err.Number <> 0 Then MsgBox "Checking the Filter in the sheets."

© Copyright Exceltipsandkeys All Rights Reserved.

Excel VBA – Get the Size of a Range

Sometimes the size of the selected range is determined dynamically. I need to check the number of rows and columns in the selected range. The following macro can do the work.

 Sub SizeOfRange()

    Dim Rng As Range

    Dim RowCount, ColCount As Integer

    '-----The size of range is determined dynamically.

    Set Rng = ActiveSheet.Range(Cells(6, 3), Cells(Cells(6, _ 3).End(xlDown).Row, Cells(6, 3).End(xlToRight).Column))

    '---- The number of rows and columns of the range

    RowCount = Rng.Rows.Count

    ColCount = Rng.Columns.Count

    MsgBox "The number of row is " & RowCount

    MsgBox "The number of Column is " & ColCount

End Sub

© Copyright Exceltipsandkeys All Rights Reserved.

Excel VBA – Copy Filtered data using Autofilter in VBA into Array

Autofilter commend can quickly filter out the information satisfied the criteria. Is there some way to select the data that are found after an autofilter using VBA?

Here, I prefer to transfer the data to an array so that I can use them in other calculation.

 Step 1, Autofilter the range

Step 2, Copy the visible cells into the array for further use

The key commend I used here is that If Rng(i,1).Rows.Hidden = False Then Copy the data into the destination array which is transferred into the main sub later.

Sub main()

    Dim FiltResult() As Variant

    Dim Filtersheet As Worksheet

    Dim FiltRange As Range

    Dim FiltField As Integer

    Dim FiltCrit As Variant

    Set Filtersheet = sheets("Sheet1")

    Set FiltRange = Filtersheet.Range("C6:F7380")

    FiltField = 1

    FiltCrit = 910

    FiltResult = Copyfilter(Filtersheet, FiltRange, FiltField, FiltCrit)

    '''''

    ''''Codes related with the filtered result

    ''''

End Sub

Function Copyfilter(Sht As Worksheet, Rng As Range, FField As Integer, Crit As Variant)

         Dim Arr() As Variant

           Sht.Select

         With Sht

          'Remove any existing filters

 Rng.AutoFilter

           RowCount = Rng.Rows.Count

           ColCount = Rng.Columns.Count

           Count = 1

           ReDim Arr(1 To RowCount, 1 To ColCount)

           Rng.AutoFilter Field:=FField, Criteria1:=Crit

            '------Copy the data to array for future use

           For i = 2 To RowCount

                If Rng(i, 1).Rows.Hidden = False Then

                   For j = 1 To ColCount

                   Arr(Count, j) = Rng(i, j).Value

                   Next j

                   Count = Count + 1

                 End If

            Next i

          'Remove the filter

           Rng.AutoFilter

        End With

        '----Transfer the data to the main sub

        Copyfilter = Arr

End Function

                                                                                 © Copyright Exceltipsandkeys All Rights Reserved.