TIPS AND TRICKS

How to make Dynamic range

A Dynamic range is a range in Excel that expands automatically when you add a value to the range. This is particularly useful when you are working with a continuously changing data set with formula. So, when you change the data, the formula will reflect to the new range. The trick is change the range formula to OFFSET and COUNTA fuction. When you add a value to the range, the range in COUNTA increases. As a result, the range returned by the OFFSET function expands. Example:

TotalGL is defined as the name of range of gain/loss column. Total G/L is calculated as SUM(TotalGL). Since GLamount range is defined as dynamic range, whenever new transaction is added to the row, the Total G/L amount is automatically included in calculation as well.

1. Define the range as you normally do. Select Cell I3 to I6. On Name box (Upper left corner), enter the range name as “TotalGL”

2. On Cell K3, enter the formula: =SUM(TotalGL). The total shows as 1,515.40. *At this point, if you ad row 7 with any another transaction, the total will remain unchanged.

3. Modify the range formula. Select Formulas on the toolbar -> Name manager -> Select TotalGL -> on “Refers to box” replace -> =Sheet1!$I$3:$I$7 to

=OFFSET(Sheet1!$I$3,0,0,COUNTA(Sheet1!$A:$A),1)
OFFSET(Reference, rows, cols, [Height], [width])

4. Click Yes to confirm the changes

Make the ExcelSheet hidden

Are you exasperated because you cannot find the spreadsheet one of your formulas refers to? The sheet does not appear among other tabs at the bottom of your workbook, nor does it show up in the Unhide dialog box. Where on earth could that sheet be? Simply, it is very hidden.

What the difference between hidden and very hidden sheets? To unhide a sheet that was hidden is very simple by right-clicking any visible worksheet, click Unhide, and select the sheet you want to view. However, a very hidden sheet cannot be made visible via the Excel user interface, the only way to unhide it is with VBA via changing its Visible property and VBA code.

1. By changing its Visible property

1. Go to Visual Basic editor by pressing Alt + F11.
2. Press F4 or click View> Properties.
3. In the Project Explorer window, select the worksheet to make very hidden.
4. In the Propertieswindow, set the Visible to 2-xlSheetVeryHidden.

2. Make active worksheet very hidden with VBA code

Sub VeryHiddenActiveSheet()
ActiveSheet.Visible = xlSheetVeryHidden
‘To make the sheet visible again, set to xlSheetVisible

End Sub

3. Make multiple worksheets very hidden with VBA code

Sub VeryHiddenSelectedSheets()

Dim wks As Worksheet
On Error GoTo ErrorHandler

    For Each wks In ActiveWindow.SelectedSheets
        wks.Visible = xlSheetVeryHidden
    Next
   Exit Sub

ErrorHandler:
MsgBox “Must contain at least 1 visible worksheet”, vbOKOnly, “Unable to Hide”

End Sub

UDF functions

UDF is simply a function in VBA excel. You can use this custom function just like Excel built-in function. UDF helps you to expand the existing functionality of Excel to cater to your specific needs and get it to do things that just doesn’t do with out of the box. This can be particularly useful for advanced mathematics, interest rate calculation or text manipulation or date calculations.

Example:

Suppose you have cell contains numbers with dashes and on the next column you need those number with no dashes. Create GetNumeric function shown below.

Create VBA code as follow

Function GetNumeric(CellRef As String) As String
    Dim StringLength As Integer
    StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
    GetNumeric = Result
End Function

Then, on the ExcelSheet you enter the function:

If you create the macro and save the ExcelSheet with the extension file name as XLSM, then this UDF is only available on this particular sheet only. You might have the situation where the function only available to a specific workbook or you want to make the function seamless to the user and you would rather implement the function globally to all users. There are several methods in doing that:

Method #1 – Saved as: XLSM (Macro) or XLSB (Binary).

This method will make UDF function available on distributed workbook only. You need share this Excel file to other user if you want to share the UDF to other user.

Method #2 – Saved inXLSTART directory

This method allows the users to share the UDF on any opened workbook on the same PC. The UDF function will be available once you start to open any workbook. By default anytime you save the file as XLAM file (eXcel Add-in), Excel will take you to default folder and save in the file on the following path,

“C:\Users\JohnSmith\AppData\Roaming\Microsoft\AddIns“.

You need to save the file in
“C:\Users\JohnSmith\AppData\Roaming\Microsoft\Excel\XLSTART“

Method #3 – Configure in Add-in

There is a better way to activate this UDF functions automatically for all users by installing this custom functions in Add-in.

1. Save the file as XLAM workbook in default directory:
“C:\Users\JohnSmith\AppData\Roaming\Microsoft\AddIns”

2. Activate your UDF.
File -> Options -> Add-ins -> Manage -> select Excel Add-ins and
click Go -> Select (Check) your UDF -> OK

Protecting addin
To protect your intellectual property and/or stop users messing about with your code

- - Open the Excel Workbook that contains UDF function.
  - Go to Developer –> Visual Basic or simply enter Alt+F11.
  - In Visual Basic editor, go to Tools -> VBAProject Properties
  - Click the Protection tab and then check “Lock project from viewing”
  - Enter your password and again to confirm it.

After doing this you must save, close & reopen the Workbook for the protection to take effect.

Delete all blank rows at once

1. Select the range of cells (Shift and drag the mouse down)
2. Press F5 (Go To Special)
3. Click Special button
4. Select radio button Blanks and click OK. Excel now highlight all empty rows
5. Right click and Delete
6. Select “Shift cells up” and click OK

Replaces blank cells with string

Copy cell to all blank cells

Method #1:

1. Select the cells with formula and hover the mouse over small square at

lower bottom corner.
2. Hold and drag down to the range that you want to copy to

Method #2:
1. Select the cells with formula and hover the mouse over small square at

lower bottom corner.

2. Double click lower bottom corner of the cell