Timeline for Reusable VBA macro to prevent Excel from destroying the data (+ backup capacity)
Current License: CC BY-SA 3.0
6 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 29, 2016 at 13:56 | comment | added | PeterT |
@BKSpurgeon - you could try set allBlankCells = ActiveSheet.Range("A1:D100").SpecialCells(xlCellTypeBlanks), then allBlankCells.NumberFormat = "0.00" or any other cell formatting you wish to apply.
|
|
| Jun 28, 2016 at 23:21 | comment | added | BenKoshy | @PeterT - excellent. I noticed that you are iterating through the cells. would you know if there a way of selecting all blank cells and applying formatting changes without iterating in Excel. | |
| Jun 28, 2016 at 23:17 | comment | added | Zaplatki | Dear All! The only questions to the community which remain: 1) what do you think about the goal itself (use VBA to prevent Excel from corrupting the data)? 2) What do you think about the idea we implemented to accomplish it (intervening into Excel's algorithm to store strings in cells by adding/removing special characters basing on our understanding:to make Excel to "think" the data should not be changed)? 4) Have you played with the workbook and would you use it for your purposes and why? 5) The quality of implementation you covered, I see the areas for improvement and will update it ;-) | |
| Jun 28, 2016 at 23:10 | comment | added | Zaplatki | @ PeterT, very helpful! I will follow all of these guidelines, especially thisCell and allCells as in many examples on the web this difference is not pointed out. The arrays syntax was tricky for me, now I understand. I will mark this as an answer after I thoroughly test the new code and come up with corrections (if any)! | |
| Jun 28, 2016 at 18:41 | comment | added | Mathieu Guindon |
Nice answer. IMO the simple fact that CreateBackup starts with a verb should be enough to say that's a method call - the explicit Call syntax/statement is indeed obsolete/deprecated since the advent of implicit procedure calls a long, long time ago. =)
|
|
| Jun 28, 2016 at 18:23 | history | answered | PeterT | CC BY-SA 3.0 |