Tips and Tricks

TIPS AND TRICKS

How to make Dynamic range

Ratings: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

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.

OFFSET(Reference, rows, cols, [Height], [width])

OFFSET(Sheet1!$I$3,0,0,COUNTA(Sheet1!$A:$A),1)

Reference: $I$3, – Gain and Loss starting Column range reference
Rows: Set offset to 0
Cols: Set offset to 0
Height: COUNTA($A:$A) –  Counts the number of cells in column A that are not empty.
width: Set to 1.

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.

After Dynamic range implementation

Implementation:

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)

4. Click Yes to confirm the changes

Now, add new transaction on row 7 (RAD transaction) and you will see the Total G/L is automatically updated as well from 1515.40 to 1963.40

 Make the ExcelSheet very hidden

Ratings: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

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

Implementation:

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

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

2a. 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 “A workbook must contain at least one visible worksheet.”, vbOKOnly, “Unable to Hide Worksheets”

End Sub

Custom User Defined Functions (UDF)

Ratings: (No Ratings Yet)

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.

Implementation

Suppose you have cell contains credit cards numbers where the user is allowed to enter with dashes or without dashes. As long as it is entered with 16 digits, it is a valid credit card numbers

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

  

How to make UDF functions available to the user

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. The workbook cannot be saved with XLSX file extension or you will lose your UDF function.

Method #2 – Saved inXLSTART directory

By default anytime you save XLAM file, Excel will take you to default folder to save in :

C:UsersleonardoAppDataRoamingMicrosoftAddIns. Instead, save the file in

C:Users\AppDataRoamingMicrosoftExcelXLSTART

This method will make UDF function available once you start to open any workbook.

Method #3 – Save the file as XLAM  (Add-in)

Once you save and the workbook as: XLAM and you can open this workbook to activate the UDF functions. This method will make UDF function available on any open workbook. Once all active workbooks are closed, UDF functions is no longer active.  You need to launch UDF workbook again every time you need to use. However, there is a better way to activate this UDF functions automatically for all users by installing this custom functions in Add-in.

 

Method #4 – Load UDF workbook file in add-in module

  1. Save your XLAM workbook in default directory:
    C:Users\AppDataRoamingMicrosoftAddIns
  2. Activate your UDF.  FILE -> Options -> Add-ins -> Manage -> select Excel Add-ins -> 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
      • go to Tools>Macro>Visual Basic Editor (Alt+F11).
      • In VBE go to Tools>VBAProject Properties
      • Click the Protection page 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.

*To make UDF available in VBA – Add as reference

Delete all blank rows at once

Ratings: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Before Implementation
 After Implementation

Steps:
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 all blank cells with string
Ratings: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Steps:
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. Enter the text you want
6. Press Ctrl+Enter
 

Copy cell to all blank cells 

Ratings: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

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