view · edit · history · print

Bad habits...

Be aware: all versions of Excel contain far more rows than the Application can ever handle in a decent way. Newer versions, far exceed [with millions of rows] the internal Applications capabilities of 65536. Excel can only track 65536 dependencies to unique references for automatic calculation and filtering. Need more? Use access or a real database!

See also google "excel bad practices" -- some are a bit silly, but not the ones above :-). Off-course, there are situations where the best practices may not apply; but only if you know why and did think about it without getting tricked by convenience.

no add-on

  • rowliner can be done with some excel code (see below: Highlight selected row or column in Excel)
  • XLTools Sheet Switcher can be done with ctrl+pgup/pgdown or by right-clicking on the bottom-left arrows.
  • switch between 2 tabs can be done by opening "new window" (in view) and view side by side or use some vba, here.
  • trim spaces can be done with formula functions (see below clean and trim)

Reference websites

Excel formula


  • you need a function add-in i think : Analysis Toolpak
  • withouth add-in

in-cell graph (does not work wit negative numbers):

  • A1=multiplier(0,1)
  • A2=first value

het verschil:

  • A=old value
  • B=new value
 delta   =B-A
 %delta  =-(1-B/A)*100 OR ((B3/A3)-1)*100 <= het verschil in procent
 pct(%)  =B/A*100

alternating rowcolors:

  • use conditional formatting formula
 =IF(OR(((MOD(ROW();4))=1);((MOD(ROW();4))=2));1;0) = bad math but it works
 =MOD(MOD(ROW();4);3) = bad math but it works

convert EURO format:

  • example: "66,55- EURO"
  • result: "-66,55"
 =VALUE(IF(ISERR(FIND("-";A1));LEFT(A1;FIND(" ";A1)-1);CONCATENATE("-";LEFT(A1;FIND("-";A1)-1))))

convert example:

  A                       B             C        D                E             F          G
1 text string:            position(-)   length   extract          number?       value      number?
2 formula:                =FIND("-",A3) =LEN(A3) =MID(A3,B3+1,C3) =ISNUMBER(D3) =VALUE(D3) =ISNUMBER(F3)
3 apple - 2,100,000       7             17       2,100,000        FALSE         2100000    TRUE
4 red orange - 915,000    12            20       915,000          FALSE         915000     TRUE

in this example =MID(A3,B3+1,C3) could also be written as =RIGHT(A3,C3-B3-1).

  • left value of "-" =MID(E5;1;FIND("-";E5)-1)
  • right value of "-" =MID(E5;FIND("-";E5)+1;LEN(E5))

encapsulate left and right values xxxxx = MID(...)

  • =IF(ISBLANK(E5);0;VALUE(xxxxx))

Excel VBA

Paste in appropriate excel sheet object

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim Row             As Long
    Dim myrange         As Range

    Cells.Interior.ColorIndex = xlNone
    Cells.Borders.ColorIndex = xlNone

    Row = Target.Row

    Set myrange = Range("A" & Row, "II" & Row)

    myrange.Interior.ColorIndex = 15
    myrange.Borders.ColorIndex = 1

End Sub

Paste in appropriate excel sheet object

Option Explicit 

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 

    Dim RngRow          As Range 
    Dim RngCol          As Range 
    Dim RngFinal        As Range 
    Dim Row             As Long 
    Dim Col             As Long 

    Cells.Interior.ColorIndex = xlNone 

    Row = Target.Row 
    Col = Target.Column 

    Set RngRow = Range("A" & Row, Target) 
    Set RngCol = Range(Cells(1, Col), Target) 
    Set RngFinal = Union(RngRow, RngCol) 

    RngFinal.Interior.ColorIndex = 6 

End Sub 


Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If ActiveCell.Interior.ColorIndex = xlNone Then
ActiveCell.Interior.ColorIndex = 40 ' tan...sort of
ActiveCell.Interior.ColorIndex = xlNone
End If
End Sub

Macro to hide columns and the form Scroll Bar objects

Sub hide1()

    Dim counter As Long

    ' When columns is applied to a Range object that's a multiple-area selection, 
    ' this property returns columns from only the first area of the range.
    For counter = 1 To 4
        Selection.EntireColumn.Hidden = True
    Next counter

    Dim sOptBut As Shape

    For Each sOptBut In ActiveSheet.Shapes
        If sOptBut.FormControlType = xlScrollBar Then
             sOptBut.Visible = msoFalse
        End If
    Next sOptBut

End Sub

sort columns individually of each other for box and wiskers diagram sample


' sort columns individually of each other for box and wiskers diagram sample
' make sure data samples are transposed from top to bottom
' select top row from left to right accross the dataset

' 5 6 7 8 <= select from left to right
' 4 4 8 9
' 5 8 6 7

For Counter = 1 To Selection.Count


    Selection.Sort Key1:=Range(ActiveCell.AddressLocal), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    ' if headers ar posing problems user 'Header:=xlNo' instead of 'Header:=xlGuess'


Next Counter

End Sub

How to convert date/time to seconds

Times are stored internally by Excel as fractions of a 24-hour day, so that 12:00:00 would be stored as 0.5 and 18:00:00 as 0.75. Consequently, to convert a time to seconds, you must multiply by the number of seconds in a day - it is easier to remember this as *24*60*60 rather than the actual number (86400).

How to list environment Variables in Excel

Tools > Macro > VB Editor

actions: double click on sheet2 > paste below subroutine and [run] > then switch back to sheet2 in excel to display results.

Sub test()

Dim EnvString As String
Indx = 1
    EnvString = Environ(Indx)
    Cells(Indx, 1) = EnvString
    Indx = Indx + 1
Loop Until EnvString = ""

End Sub

Get only 1 var and optionally show in msgbox

Dim ENVIR As String
ENVIR = Environ("SNC_LIB")

Find row with text OR last row containing data and use it

    ' find text "END OF TODO" and jump to it
    'Cells.Find(What:="END OF TODO", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ' => alternative to above: goto last row with data
    'lastrow = ActiveCell.Row
    ' => alternative to above: no select required
    lastrow = Range("a65536").End(xlUp).Row
    ' => example usage for lastrow: Range("B" & lastrow).Select
    ' => example usage for lastrow: Range("A" & lastrow & ":B" & lastrow).Select

Find column with text and use it

    Cells.Find(What:="sometext", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate

    curcol = Left(ActiveCell.EntireColumn.Address(columnabsolute:=False), Application.WorksheetFunction.Find(":", ActiveCell.EntireColumn.Address(columnabsolute:=False)) - 1)

Disable Addins

AddIns("RowLiner 4.1").Installed = True
AddIns("RowLiner 4.1").Installed = False

Shifting rows up and down.

attach shortcut ctrl key combination: tools > macro > macros > select macro > options

Sub row_up()

    curcol = ActiveCell.EntireColumn.Address(columnabsolute:=False)
    curcolleftsize1 = (Len(curcol) - 1) / 2
    curcolnum = Left(curcol, curcolleftsize1)

    currow = ActiveCell.EntireRow.Address(rowabsolute:=False)
    currowleftsize1 = (Len(currow) - 1) / 2                   '    currowleftsize2 = Application.WorksheetFunction.Find(":", currow) - 1 'alternative
    currownum = Left(currow, currowleftsize1)

    currownum_finalposition = currownum - 1
    currownum_insertrow = currownum - 1

    Rows(currow).Select                                       '    Range(currow).Select 'alternative to row select
    Rows(currownum_insertrow & ":" & currownum_insertrow).Select
    Selection.Insert Shift:=xlDown

    Range(curcolnum & currownum_finalposition).Select

End Sub

Sub row_down()

    curcol = ActiveCell.EntireColumn.Address(columnabsolute:=False)
    curcolleftsize1 = (Len(curcol) - 1) / 2
    curcolnum = Left(curcol, curcolleftsize1)

    currow = ActiveCell.EntireRow.Address(rowabsolute:=False)
    currowleftsize1 = (Len(currow) - 1) / 2                  'currowleftsize2 = Application.WorksheetFunction.Find(":", currow) - 1 'alternative
    currownum = Left(currow, currowleftsize1)

    currownum_finalposition = currownum + 1
    currownum_insertrow = currownum + 2

    Rows(currow).Select                                      'Range(currow).Select 'alternative to row select
    Rows(currownum_insertrow & ":" & currownum_insertrow).Select
    Selection.Insert Shift:=xlDown

    Range(curcolnum & currownum_finalposition).Select

End Sub

increase fs relative to the current size

  A		B	C
1 Filesystem	curr_GB	new_GB
2 /sapmnt	1	1
3 /usr/sap	4	5
4 /sapdb	1	1
5 /usr/sap/ACC	5	7
6 /sapmnt/ACC	3	4
7 /sapdb/ACC	20	29
8 SUM		34	50

C8=<new total>

Calculate quarter based on date field (<year>Q<#> ; 2011Q2)


Formulla fill down

1.Type a number, such as 395.54 into cell D1 in Excel.
2.Press and hold down the Shift key on the keyboard
3.Press and hold down the Down Arrow key on the keyboard to extend the cell highlight from cell D1 to D7.
4.Release both keys.
5.Press and hold down the Ctrl key on the keyboard.
6.Press and release the " D " key on the keyboard.
7.Cells D2 to D7 should now be filled with the same data as cell D1. 

Subtract date and time

20/03/2012 13:48 - 10/02/2012 11:49 = 937:58
where formula for 937:58 is =B1-B2 with formatting [u]:mm or [hh]:mm
you can also use the function =days360(B1;B2) and format as number

always clean and trim


Get URL from excel links

  • create workbook module and paste below function
  • use function as =hlink(A1)
Function HLink(rng As Range) As String
'extract URL from hyperlink
'posted by Rick Rothstein
  If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function

Create a Search Box in Excel without VBA

Set new conditional format rule and use a formula =and(ISNUMBER(SEARCH($C$1;A4));$C$1<>"")


find a sub-string in a range


Show excel formullas

  • create workbook module and paste below function
  • use function as =showf(A1)
Function ShowF(Rng As Range)
ShowF = Rng.Formula
End Function

Color conversion from hex codes in excel

Sub SetHexColors()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
Cells(i, "B").Interior.Color = HEXCOL2RGB(Cells(i, "A"))
End Sub

Public Function HEXCOL2RGB(ByVal HexColor As String) As String
Dim Red As String, Green As String, Blue As String
HexColor = Replace(HexColor, "#", "")
Red = Val("&H" & Mid(HexColor, 1, 2))
Green = Val("&H" & Mid(HexColor, 3, 2))
Blue = Val("&H" & Mid(HexColor, 5, 2))
HEXCOL2RGB = RGB(Red, Green, Blue)
End Function

convert (date) time to decimal hours

# simply multiply it by 24. Set formatting to number and two places of decimal.

PS: and back... /24 for to convert decimal hours back to days and format as time field.

fill down if blank

Calculate average of values in a relative position

calculate AVERAGE
of 1 column to the left of the current column "COLUMN()-1"
starting 2 rows down "ROW()+2"
for the next 10 rows, ending 12 rows down "ROW()+2+10"


ADDRESS(;;4) = returns relative adresses like A1 instead of absolute $A$1
INDIRECT evaluates the ADDRESS as a reference, otherwise the result of ADDRESS would be seen as a text string.

find reference of cell containing string

find row "MATCH"
containing next string "*Ref#"
down in current column "COLUMN()"
starting 2 rows down "ROW()+2"
ending at row "1000"


ADDRESS(;;4) = returns relative adresses like A1 instead of absolute $A$1.
INDIRECT evaluates the ADDRESS as a reference, otherwise the result of ADDRESS would be seen as a text string.
MATCH(;;0) = finds the first value that is exactly equal to the lookup value.

do something (filter) if a cell changes

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("b4")
If Not Application.Intersect(keycells, Range(Target.Address)) _
    Is Nothing Then

    If WorksheetFunction.CountA(Range("b4")) = 0 Then
        On Error Resume Next
        On Error Resume Next
        ActiveSheet.Range("$A$4:$DO$5000").AutoFilter Field:=3, Criteria1:="TRUE"
    End If
End If   
End Sub

excel field formatting

if the date is not set and therefore 0 you can use the custom format: yyyy-mm-dd;;;@

Month in English (409), no matter what language setting you have:


Sum in a column if a certain condition in another column is met


...sums all fields in i:i if the field in column c:c is the same as on your current line in row 11 (field c11).

Example timesheet with different example formulas including /day /week calculations

AM/PM	date            week	start	end	hh:mm   TOT/day h       h/day   h/week
AM	5/09/2016	36	9:14	15:59	6:45		6,75	/	/
PM	5/09/2016	36	17:14	20:55	3:41	10:26	3,68	10,43	/
AM	6/09/2016	36	11:04	12:00	0:56		0,93	/	/
PM	6/09/2016	36	12:30	17:31	5:01	5:57	5,02	5,95	/
AM	7/09/2016	36	9:04	12:00	2:56		2,93	/	/
PM	7/09/2016	36	12:30	18:35	6:05	9:01	6,08	9,02	/
AM	8/09/2016	36	9:17	12:00	2:43		2,72	/	/
PM	8/09/2016	36	12:30	18:41	6:11	8:54	6,18	8,90	/
AM	9/09/2016	36	9:14	12:00	2:46		2,77	/	/
PM	9/09/2016	36	12:30	18:45	6:15	9:01	6,25	9,02	43,32
AM	12/09/2016	37	9:14	15:59	6:45		6,75	/	/
PM	12/09/2016	37	17:14	20:55	3:41	10:26	3,68	10,43	/
AM	13/09/2016	37	11:04	12:00	0:56		0,93	/	/
PM	13/09/2016	37	12:30	17:31	5:01	5:57	5,02	5,95	16,38
						        =IF(B2=B1,SUM(F1:F2),"")   <=== this causes problems if you insert or have +- é lines, use indirect!

Chart including dates with no data

steps for data

  • make pivot (count of date)
  • Group by month and year
  • pivot > design > report layout > repeat all item labels
  • field settings > layout > "Show items with no data" checkbox
  • pivottable options > layout > "For empty cells show" 0 (or -1)
  • filter out the report endpoints (example: <1/1/2017 and >12/16/2017)

optional graph settings

  • show data labels outside (zero values will also show)
  • if empty cells set to -1 then you can change the vertical axis option minimum to 0, this will hide the -1 data labels
  • if data labels are shown, the vertical axis can be hidden
  • other markup: set width of bar and set border color


Extracting Integers and Fractions in Microsoft Excel

To extract the integer value, use the formula:
To extract the fractional value, use the formula:
=MOD(A1,1) or =(A1-INT(A1))

usage of F4 when selecting a cell

the selection will cycle through the possible assignments...


Excel Highlight rows and records

Conditional range with following formula where A1 is the list drop down for values in C:C


Highlight selected row or column in Excel (minimal, 1 line, VBA in workbook)

This is usually done with a VBA solution but we can do it without VBA.


  • clrl+z = undo
  • you can even avoid the VBE when you press F9 (recalculate) after each change
  • Conditional values (applies to =$A:$AZ)
  • new Conditional formatting rule > use a formula ...
=ROW()=CELL("row")     <=optional, set conditional format for example to: "red top border"
=COLUMN()=CELL("col")     <=optional
Application.ScreenUpdating = True     <=optional, alternate use F9 to refresh

Automate the above (for row with top red border) - oh the irony of implementing a non-VBA solution with a VBA macro :-)

Sub SetHighlights()
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub


very hidden sheets

shortcut for sheet / tabs menu

create macro; add macro option for shortcut ctrl+t (tab).

Sub ShowSheetLists()
  Application.CommandBars("Workbook tabs").ShowPopup
End Sub

see also:

Saving this to be an add-in.

  • save as *.xla
  • In VBE select your workbook/project the expand and select ThisWorkbook and View > Properties Window.
  • Change "IsAddin" to False
  • <make changes>
  • Change "IsAddin" to True
  • (re)save as *.xla

ATTENTION, custom add-ins may be restricted, see here for policy and registry keys: (typical error: The add-in you have selected is disabled by your system administrator.)

Change find default from "formula" to "value" -- untested!!

Application.Dialogs(xlDialogFormulaFind).Show , 2

Use VBA to Add a Custom Command to the Ribbon

conditional format if cell value is matching a cell in some column

Apply conditional format with formula to column B:B to find the values in column A:A:


if replacing VLOOKUP with INDEX & MATCH is not enough...

Basicaly INDEX&MATCH can replace VLOOKUP like so:

is the same as

But when using INDEX&MATCH you can use multiple criteria using the flexibility of MATCH:

Contrary to what i found on the internets, this does not work for me:
=INDEX(G:G,MATCH(1,(E:E=A1)*(F:F=B1),0))  <=== DO NOT press [ENTER]... press [ctrl]+[shift]+[enter]

But the internets are full of alternatives, and this does work for me:
=INDEX(G:G;MATCH(A1&B1;E:E&F:F;0))  <=== DO NOT press [ENTER]... press [ctrl]+[shift]+[enter]

The below lookup is working great but is not scalable; it can truly slow down your excel to a grinding halt!!
=INDEX(sheet!J:J&"d, "&sheet!G:G;MATCH(G172;sheet!D:D;0))
speedup is to use 2 columns, like so:

Stop searching for "Show page breaks" in options, create ribbon macro shortcut to...

Sub TogglePageBreaks()

  ActiveSheet.DisplayPageBreaks = Not ActiveSheet.DisplayPageBreaks

End Sub

force “F2”+“Enter” on range of cells

Sub ApplyF2()
    Selection.Value = Selection.FormulaR1C1
End Sub

PS: FormulaR1C1 function is magic to me - so don't ask.

how can i use COUNTA with a filter?


problem: Sudden Excel format change general to date

To resolve future issues:

ribbon tab:Home
ribbon section: Styles
Right-click: "NORMAL"-style
Select: Modify
Click: Format button
tab: Number
select: General

It did the trick with SOME cells, but most cells (that used to be in "General" format) remained in "Date" format.

UNTESTED: If you still have some cells in date format you want to change back to general:

1- Right-click on any black cell
2- Choose "Format Cells..."
3- In the "Number" tab, select "Custom"
4- Delete formats starting with "[$-409]..." of whatever the format is that is causing issues

UNTESTED: you can also try this

Sub CleanStyles()
Dim sty As Style, wbTemp As Workbook
' First, remove all styles other than Excel's own.
' they may have arrived from pasting from other workbooks
For Each sty In ThisWorkbook.Styles
   If Not sty.BuiltIn Then sty.Delete
'Second, revert the remaining styles to Excel's default for a new workbook
Set wbTemp = Workbooks.Add
Application.DisplayAlerts = False
ThisWorkbook.Styles.Merge wbTemp
wbTemp.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub

UNTESTED: another simple solution

click "cell styles"  select "merge styles".  then select an existing file that has the desired normal format.

I simply opened a new spreadsheet and saved it, then used this file as the merge file.  it fixed all the formatting in the messed up file.  (I did save a spare version of my target file before doing this.)

UNTESTED related?

...alternatively we need a vbs macro that can: convert all empty fields having a date format to general format.

Howto display cell format in status bar of excel?

  • Open VBA editor
  • goto wanted add-on xla or PERSONAL.XLSB
  • insert class module
  • view properties window
  • renames class module to clsAppEvents
  • add below code in class module "clsAppEvents"
Option Explicit

Private WithEvents App As Application

Private Sub Class_Initialize()
    Set App = Application
End Sub

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Ctype      As String
    If Selection.Cells.Count > 1 Then
         Ctype = "Multiple cells"
         Ctype = Target.NumberFormat
         If Ctype = "General" Then
            If Target.Value = Target.Text Then
               Ctype = "General(TEXT)"
               Ctype = "General(NUM)"
            End If
         ElseIf Ctype = "@" Then
               Ctype = "@(TEXT)"
         End If
    End If
    Application.StatusBar = Ctype
End Sub

  • Open regular module
  • enter below code
Option Explicit

Dim oApp As clsAppEvents

Sub InitAppEvents()
    Set oApp = New clsAppEvents
End Sub

  • save all
  • open new excel
  • trigger macro "InitAppEvents" - this have to be triggered every time you want this feature to function.
    • most easy way to trigger this macro is to add the macro to a custom ribbon (via choose commands from: "macro")
  • see status bar changes on left side for each cell selected

Debug messages in vba

'sample 1
Debug.Print Format(Now(), "yyyyMMddThhmmss") & " debug line trigger 999 " & Sh.Parent.Name & "/" & Sh.Name

Private Sub somemacro(ByVal Target As Range)
Debug.Print Format(Now(), "yyyyMMddThhmmss") & " selection on: " & Target.Address(0, 0, , True)

To display messages in VBA editor: view > immediate window. Sample2 output:

  • 20180717T233023 selection on: '[1. CRH Activity tracking.xlsx]Pipeline'!C40

Adding a "comments" column to a data source table

(personally using excel 2016 power query)

less attractive alternative

You could achieve this with a separate query which would export the notes and the corresponding item code to a separate table. This table would be merged with your source data so that you would include the previously captured notes each time you perform a refresh.

Using helper columns for multi-criteria lookups

Be aware: a more simple solution is possible, by sorting the table "CommentDate". This cannot be guaranteed and is not desired in this use-case; So a helper column is used for combination of 2 criteria.

1) We want in table TableView1 to add the most recent comment for column Number.

2) Sample lookup TableComments1 with comments history:

T.Number      | Comment      | CommentDate | Helper1
SCTASK0073347 | correction   | 22/07/2018  | 43303SCTASK0073347
SCTASK0073347 | find this!   | 25/07/2018  | 43306SCTASK0073347
SCTASK0073347 | update 2     | 21/07/2018  | 43302SCTASK0073347
TASK9999      | comment      | 25/07/2018  | 43306TASK9999

3) Formula breakdown

The formula for the Helper column just does CONCATENATE 2 columns:

Lets say we want: SCTASK0073347
Note: in the helper column we have value "43306SCTASK0073347"; 
where "43306" is the numerical representation of date "25/07/2018".

This will search for a match in the "Number" and return the most recent "Date":
 <<MostRecentDate>> =MAXIFS(TableComments1[CommentDate];TableComments1[T.Number];TableView1[@Number])
(abbreviated to for readability)
..returning "25/07/2018".

This will search for a combination of above formula <<MostRecentDate>> & "Number" in the Helper column:
..returning row (2) with helper table "43306SCTASK0073347".

From this point forward we use MATCH (now returning the wanted row) and INDEX in a style VLOOKUP would do:
...returning the wanted column with desired comment "find this!".

Full formula, includes IFNA to clear blank lookups with no comments:

Assign keyboard shortcuts to macros

  1. macro "option" (alt+f8), note: [shift]+<capital letter> can also be used
  2. in macro module, ctrl+t for macro "showsheetlists": application.macrooptions macro:="showsheetlists", hasshortcutkey:=true, shortcutkey:="t"
  3. with "onkey" routine and workbook open/beforeclose events. application.onkey "%{c}", "subroutine"
Sub CreateShortcuts()
    Application.OnKey "^%{c}", "InsertDate"
    Application.OnKey "^%{i}", "InsertDate"
    Application.OnKey "^+{I}", "InsertDate"
End Sub
Sub DeleteShortcuts()
    Application.OnKey "^%{c}"
    Application.OnKey "^%{i}"
    Application.OnKey "^+{I}"
End Sub

see also

List all keyboard shortcuts of personal macro subroutines

  1. export all modules (this can be done manually or scripted
  2. filter the export on the regex "^Attribute" AND "\.OnKey"
Attribute VB_Name = "Module1"
Attribute Filtervalue_TableComments1.VB_Description = "macro filter value in comments table"
Attribute Filtervalue_TableComments1.VB_ProcData.VB_Invoke_Func = "F\n14"
Attribute Update_TableComments1.VB_ProcData.VB_Invoke_Func = "u\n14"
Attribute InsertDate.VB_ProcData.VB_Invoke_Func = "i\n14"
Application.OnKey "^%{c}", "InsertDate"
Application.OnKey "^%{i}", "InsertDate"
Application.OnKey "^+{I}", "InsertDate"

Ignore the first 2 lines for now... the resulting shortcuts (using 2 different assignment methods) are
ctrl+shift+F Sub Filtervalue_TableComments1()
ctrl+u       Sub Update_TableComments1()
ctrl+i       Sub InsertDate()
ctrl+alt+c   Sub InsertDate()
ctrl+alt+i   Sub InsertDate()
ctrl+shift+I Sub InsertDate()

error using VBA code from third party



  "VBIDE.VBComponent user defined type not defined". 

In VBA, go to the Tools menu, choose References, and select:

 "Microsoft Visual Basic For Applications Extensibility Library".


  91: "object variable or with block variable not set"

Are you developing an add-in? if so temporary set:

  workbook > property > isaddin = False


  cannot execute, no access trust: "programmatic access to Visual Basic Project is not trusted"

enable access to the VBA Object Model

  excel (2016) > trust center > macro settings > Developer Macro settings > Trust access to the VBA project object model

this option may be greyed out, goto registry and search for diabled (0) accessvbom:

Registry Path	software\policies\microsoft\office\16.0\excel\security
Value Name	accessvbom
Value Type	REG_DWORD
Enabled Value	1
Disabled Value	0

change to 1 and restart excel.

it works!

get date(month) from weeknumber

C1=2020 (year)
D1=1 (monday)
D3=2 (week 2)

(custom date format:mmm)
Jan = DATE($C$1, 1, -3 + 7 * D3 - WEEKDAY(DATE($C$1, 1, 4), 2) + $D$1)

represent EURO in excel to DOLLARS or POUNDS using correct decimal notation

you are using a typical European localisation using , for decimals and . for grouping thousands.
field A1 = ",12 €"     (5000000000,1234 formatted as EURO currency or number with 2 decimal places)
field A2 = " £"                     (post-fix string, formatted as text)

=SUBSTITUTE(LEFT(TEXT(A1;"#.##0,00");     FIND(",";TEXT(A1;"#.##0,00"))     -1);".";",")     & "." &     MID(TEXT(A1;"#.##0,00");     FIND(",";TEXT(A1;"#.##0,00"))     +1;10)     &A2

result = "5,000,000,000.12 £" 

Unfortunately, The result is only a "representation" for display usage as it is formatted as text. The way the formula works is that we identify a split for decimal notation and substitute left string grouping before concatenating new decimal, right string and post-fix.

PS: I assume the new LET function for Office 365 insiders could simplify this. For now i leave it

dynamic range for excel lists with grouping or chapters

This formula searches down towards the next formula and sets the range automatically between the current and next formula in the column.

=AVERAGE( A3 : INDIRECT(   "R"   &   ROW()-1+IFNA(   MATCH("=*";FORMULATEXT(A3:INDIRECT("R"&$A$1&"C"&COLUMN();FALSE));0);    IFNA(   MATCH("x";A3:INDIRECT("R"&$A$1&"C"&COLUMN();FALSE);0);   $A$1 )   ) &"C"&COLUMN();FALSE   ))

=sum(          <=== you can use sum, min, max etc...
   A3 : INDIRECT(  "R"   &   ROW()-1+  <=== here you start your range
         IFNA( MATCH("=*";FORMULATEXT(A3:INDIRECT("R"&$A$1&"C"&COLUMN();FALSE));0);  <=== match until next cell w. formula
         IFNA( MATCH("x";A3:INDIRECT("R"&$A$1&"C"&COLUMN();FALSE);0);  <=== match until end-marker for end of data list (using "x" here)
         $A$1 )         <=== looking forward a specified tunable number of cells available in A1 (1000 is often a good number) - in case there is no end-marker.
   ) &"C"&COLUMN();FALSE )  <=== here you and your range with dynamic change of current column


  1. Paste formula
  2. change A3 to cell below formula cell
  3. change $A$1 (in 3 indirect formulas) to field for contain rows looking forward . 1000 is usually a good number.

last data line...

  • you can end the formula range with blank cells
  • you can end the formula range with cell containing: x
  • you can end the formula range with a dummy formula, even if the cell contains formula: ="x"

array data in single cell

example dataset (start in A1:A7, 1 row is 1 field)
 31258 1
 941256, 12858
 81256 - 1256 - 12
 5498 - 787 > 54564

Lookup range is G:H containing only this data for testing
 4561	aa
 31258	bb
 787	cc
 8	dd
 45	ee
 1256	ff
 5498	gg

in B1:B7, first substitute sepparators and cleanup data...

in C1:C7, we do magic to use B1 as array for lookup_value in XLOOKUP (you can also use VLOOKUP). 
Once all data is available we transpose & join all data.
        IFERROR(--(TRIM(MID(SUBSTITUTE(B1;" ";REPT(" ";100));100*(ROW(INDIRECT("1:"&(LEN(B1)-LEN(SUBSTITUTE(B1;" ";""))+1)))-1)+1;100)));TRIM(MID(SUBSTITUTE(B1;" ";REPT(" ";100));100*(ROW(INDIRECT("1:"&(LEN(B1)-LEN(SUBSTITUTE(B1;" ";""))+1)))-1)+1;100)));G:G;H:H;"x";0

in D1:D7 we do another cleanup for data not matched by XLOOKUP / VLOOKUP (remove duplicate "x")
=SUBSTITUTE(SUBSTITUTE(C1;", x";"");"x, ";"")


column:A              column:D

31258 1               bb
591258,1256           ff
941256, 12858         x
81256 - 1256 - 12     ff
5498 - 787 > 54564    gg, cc
4525,45;5/48          ee
45878,455,55,6,8      dd

jump / link / goto other cell in same sheet

you can make a hyperlink to another cell in the same sheet. For example, to insert a hyperlink that will take you to cell A1 in the same worksheet, use a formula similar to this:

=HYPERLINK("#A1", "Go to cell A1")

or something more dynamic...
jump to range of columns on row 3 using dynamic friendly text
=HYPERLINK("#"&AI3&"3";AI3&") "&AH3)

see alos:,and%20edit%20hyperlinks%20in%20Excel.

avoid repeating formula



Solution: force an error like #DIV/0! and then use IFERROR, e.g.

=IFERROR(1/(1/   SUMIFS_formula   ),"")

This also works for dates where some cells are empty or have 0 (when using an xlookup or something to avoid return of 00/01/1900).

=IFERROR(1/(1/   A1   );"-")





admin · attr · attach · edit · history · print
Page last modified on February 24, 2021, at 01:14 AM