Hey I never knew this until I read it in this blog after referencing a post by the same author in my last blog.....just highlight each bit of the formula you don't understand and press F9 and it will evaluate it for you. Undo it (Ctrl + Z) and move onto the next bit or maybe a smaller bit if you still don't understand the value its giving you.
Remarkably handy as even I don't understand what half the formulas do in my Xbrl sheets. I'd love to make them simpler but the trade off is more columns and sheets to track or worse macros ,where whats going becomes even more opaque.
Talking of macros, its kinda like using Shift + F9 to evaluate an expression when debugging one.
At least in formulas the logic is on display for all to see. And now you don't have to bust a brain cell to work out what each bit is doing. You can just press F9 and see.
Wednesday, 12 September 2018
Beware cut and paste is not the same as copy and paste
Of course its not! But you might think it was if you then delete the cell you just copied. Wrong! If you move the cell via a cut or a drag, it will drag any references in formulas to the cell with it. Sometimes this is actually what you want to happen but a lot of the time its not. And you won't now anything about it! Until you open your sheet weeks later and wonder why your formulas are riddled with Ref! errors.
You may already be aware of this through bitter experience or you may have read one of the many warnings posted on the web. This article is a particularly good explanation of the what and the why.
So if in doubt, always copy and paste then delete the original reference. If you are using our TotaliZd product (coming soon), then be careful how you move your tags around. Copy & Paste!
You may already be aware of this through bitter experience or you may have read one of the many warnings posted on the web. This article is a particularly good explanation of the what and the why.
So if in doubt, always copy and paste then delete the original reference. If you are using our TotaliZd product (coming soon), then be careful how you move your tags around. Copy & Paste!
Turning the Tables
So finally I've turned to Excel Tables. With Microsoft putting Get & Transform centre stage in their admirable efforts to establish Excel as data mashup central, I've had to succumb to their wily charms.
Tables can be a law unto themselves! You're never quite sure what they are going to do until you start playing with them.
So hear are a few things I think I've figured out:
Lesson 1: You can add columns to the front or end of your Power Query Tables and refresh the data (from your chosen data source. If you wanted it to be XBRL - you could check out our XBRL XL) and your new columns will stay the same. Both the columns and the data in them will remain and we've taken advantage of this in totaliZd (coming soon). Note you don't even have to "officially" resize the Table - start typing to the side of the Table and it will immediately be consumed into it!
Lesson 2: You can add rows to the bottom of your table and they will disappear when you refresh the data source. Bum! We had to bear this in mind in totaliZd!
Lesson 3; When you add a formula to one of your new columns that references a column coming from the data source, it appears in all the other rows. Magic!
Lesson 4: What if I want the formula only to exist in certain rows,say row subtotals. A proper sort of database like table shouldn't have subtotals of course but we're mashing up here so maybe you do.
Well you can just the delete the formulas in the rows you don't want. And no all the formulas in all the rows will not magically disappear when you do this. Phew! If we're talking a few subtotals here and there then you can delete all the formulas bar one and then copy that to the minuscule number of cells you do want to have formulas. If you are unnerved by all this Table magic, you can simply tell Excel to stop casting spells. Not with a wand but by changing the proofing options under settings to clear the Fill formulas in tables.
Lesson 5: Now I know I said if you insert a row, you will lose it on refresh (Have I not learnt anything from Lession 4!?). Well there might be times when you want to do it anyway (especially if you are dealing with a Table that wasn't created from a Data Query - it is also an option in totaliZd in one special circumstance where you need to create a total that ought to be but isn't there).
Excel has a special way of enabling you to do this within a Table that is not immediately obvious. It is not up on the Table Ribbon and you can't insert it like you might insert cells elsewhere but you can right click on the cell in the Table where you want the row (or column for that matter) and make a wish, sorry click on insert and the option will appear. Be aware that if you refresh the data after inserting a column in the middle, that WILL disappear. Its not a wizard!
Now, because I have painfully learnt Lession 4, you might want to copy that row somewhere else as well if you are planning a refresh, maybe two rows below the Table but not one row below, otherwise it will eaten by the Table and will be banished to oblivion upon refresh. Like a bad witch, Excel Tables can sometimes be malicious.
Tables can be a law unto themselves! You're never quite sure what they are going to do until you start playing with them.
So hear are a few things I think I've figured out:
Lesson 1: You can add columns to the front or end of your Power Query Tables and refresh the data (from your chosen data source. If you wanted it to be XBRL - you could check out our XBRL XL) and your new columns will stay the same. Both the columns and the data in them will remain and we've taken advantage of this in totaliZd (coming soon). Note you don't even have to "officially" resize the Table - start typing to the side of the Table and it will immediately be consumed into it!
Lesson 2: You can add rows to the bottom of your table and they will disappear when you refresh the data source. Bum! We had to bear this in mind in totaliZd!
Lesson 3; When you add a formula to one of your new columns that references a column coming from the data source, it appears in all the other rows. Magic!
Lesson 4: What if I want the formula only to exist in certain rows,say row subtotals. A proper sort of database like table shouldn't have subtotals of course but we're mashing up here so maybe you do.
Well you can just the delete the formulas in the rows you don't want. And no all the formulas in all the rows will not magically disappear when you do this. Phew! If we're talking a few subtotals here and there then you can delete all the formulas bar one and then copy that to the minuscule number of cells you do want to have formulas. If you are unnerved by all this Table magic, you can simply tell Excel to stop casting spells. Not with a wand but by changing the proofing options under settings to clear the Fill formulas in tables.
Lesson 5: Now I know I said if you insert a row, you will lose it on refresh (Have I not learnt anything from Lession 4!?). Well there might be times when you want to do it anyway (especially if you are dealing with a Table that wasn't created from a Data Query - it is also an option in totaliZd in one special circumstance where you need to create a total that ought to be but isn't there).
Excel has a special way of enabling you to do this within a Table that is not immediately obvious. It is not up on the Table Ribbon and you can't insert it like you might insert cells elsewhere but you can right click on the cell in the Table where you want the row (or column for that matter) and make a wish, sorry click on insert and the option will appear. Be aware that if you refresh the data after inserting a column in the middle, that WILL disappear. Its not a wizard!
Now, because I have painfully learnt Lession 4, you might want to copy that row somewhere else as well if you are planning a refresh, maybe two rows below the Table but not one row below, otherwise it will eaten by the Table and will be banished to oblivion upon refresh. Like a bad witch, Excel Tables can sometimes be malicious.
Tuesday, 11 September 2018
On Error Resume Next
There are alot of posts out there telling you to stay away from:
On Error Resume Next
But I say for simple macros, its the quick and incredibly easy way to stop macros behaving weirdly when someone does something (or uses it somewhere) unexpectedly. When you want to code a quick macro, you don't want to be worrying about sophisticated error trapping or coding for all scenarios. Because then its not quick. So just stick this simple line before any dodgy VBA calls and you or your users won't be faced with unexpected behaviours or subscripts out of range.
Sub FindValue()
Dim srchVal as String
Dim colHeading as String
Dim srchRange As Range
Dim foundRange As Range
srchVal = ActiveCell.Value
colHeading = Cells(1, ActiveCell.Column).Value
On Error Resume Next
ThisWorkbook.Sheets(colHeading).Activate
Set srchRange = ActiveSheet.Cells
Set foundRange = srchRange.Find(srchVal, , , xlWhole)
foundRange.Activate
End Sub
This macro searches for the value in the current cell in the sheet named at the top of its column. If there is no sheet name at the top of the column, it does weird things. But not with:
On Error Resume Next
It moves to the next line and sets the current sheet as the active sheet. So, simply and neatly, you just end up where you started. Of course its worth having a quick look at what that resumed next line of code could be coz sometimes it could end up taking you someplace weirder. And you could write lots of code to make sure this never happens, But really, its a macro - a quick and dirty way to get something repetitive done fast.
On Error Resume Next
But I say for simple macros, its the quick and incredibly easy way to stop macros behaving weirdly when someone does something (or uses it somewhere) unexpectedly. When you want to code a quick macro, you don't want to be worrying about sophisticated error trapping or coding for all scenarios. Because then its not quick. So just stick this simple line before any dodgy VBA calls and you or your users won't be faced with unexpected behaviours or subscripts out of range.
Sub FindValue()
Dim srchVal as String
Dim colHeading as String
Dim srchRange As Range
Dim foundRange As Range
srchVal = ActiveCell.Value
colHeading = Cells(1, ActiveCell.Column).Value
On Error Resume Next
ThisWorkbook.Sheets(colHeading).Activate
Set srchRange = ActiveSheet.Cells
Set foundRange = srchRange.Find(srchVal, , , xlWhole)
foundRange.Activate
End Sub
This macro searches for the value in the current cell in the sheet named at the top of its column. If there is no sheet name at the top of the column, it does weird things. But not with:
On Error Resume Next
It moves to the next line and sets the current sheet as the active sheet. So, simply and neatly, you just end up where you started. Of course its worth having a quick look at what that resumed next line of code could be coz sometimes it could end up taking you someplace weirder. And you could write lots of code to make sure this never happens, But really, its a macro - a quick and dirty way to get something repetitive done fast.
Subscribe to:
Posts (Atom)