A
adamsjm
While my main expertise is in Process Improvement, I have become an intermediate MS Excel Application developer in order to implement many of the solutions. Having read this forum for the past couple of months and the questions about MS Excel posted on it, I thought I would pass on some MS Excel web sites to use and learn from, a list of books to read, and tools which can be added into Excel to speed up your application / project development.
MS Excel is an extremely versatile and powerful tool. You can even develop .xll (similar to .xls files) functions in C+ /C++ and integrate them directly into Excel. This process is used on Wall Street to value and assess Derivative Trading. While we will probably never obtain that level of expertise, we can greatly improve our effectiveness and efficiency along the way.
Websites
The most important web site to bookmark is a listing of Excel’s Most Valuable Programmers. These are independent Excel developers who do not work for MS, but make their living developing Excel applications. They have proven themselves in their knowledge and their willingness to share it.
► https://www.mvps.org/links.html#Excel
From that list the best “General Interest How-to Sites” (especially for new learners) are:
► https://www.cpearson.com/
► https://www.contextures.com/tiptech.html
► https://www.j-walk.com/ss/
► https://www.mvps.org/dmcritchie/excel/excel.htm
► https://www.exceluser.com/index.htm
► https://www.dailydoseofexcel.com/
The “Higher End How-to Sites” are:
► https://www.appspro.com/ - Rob Bovey
► https://www.oaltd.co.uk/ - Stephen Bullen
Rob and Steven are, in my opinion, the best Excel developers / experts in the world.
► https://www.datapigtechnologies.com
► https://www.jkp-ads.com/
► https://www.tushar-mehta.com/
The outstanding “Graphing and Charting How-to Sites” are:
► https://www.peltiertech.com/
Jon is a metallurgist by training, with a Doctor of Science degree from MIT. He has over 20 years experience in research and development, and more recently in manufacturing and production support, in the aerospace, automotive, and industrial parts industries. His background includes Statistics, Total Quality, and Six Sigma. He has developed many quality charts and has posted the how-tos on his web site.
► https://www.andypope.info/
Tip for searching the Web: How do you get a good response to your web search? When you are seeking a “how-do-I” start your search with "MVP", enter your key words that you are searching, and then finish with “*excel*”. By doing this your responses will have an answer from an Excel MVP which will be correct.
Books
The following books are targeted for Excel 2000-2003. The same authors have written companion books for Excel 2007. The books are listed in difficulty order – low to high.
[See the attached Word document or .jpg files for the book images.]
Add-In tools
These Add-in tools can greatly improve your productivity. And the best part is they are FREE.
Rob Bovey once told me, “If you find a tool that you need and they are charging for it, keep searching. There will be a better one free of charge.”
PopTools ► https://www.cse.csiro.au/poptools/
Greg Hood wrote the statistic tool add-in PopTools. MS Excel’s statistical functions are not noted for their robustness and accuracy. If you require the best in statistical tools, add PopTools to your Excel. It has been written as an Add-in so that you can see his methodology and as a compiled for fast execution (a ,xll).
[From the web site]
PopTools is a versatile add-in for PC versions of Microsoft Excel that facilitates analysis of matrix population models and simulation of stochastic processes. Included in PopTools are routines for iterating spreadsheets. These make it possible to run Monte Carlo simulations, conduct randomisation tests (including the Mantel test) and calculate bootstrap statistics. The routines include array formulas for matrix decompositions (Cholesky, QR, singular values, LU), eigenanalysis (eigenvalues and real eigenvectors of square matrices) and formulas for generation of random variables (eg, Normal, binomial, gamma, exponential, Poisson, logNormal). Many of these functions depend on Jean DeBord's TPMath library - a Pascal numerical library - which has been compiled into a DLL accessed via Excel.
Add-in tools from Rob Bovey ► https://www.appspro.com/
XY Chart Labeler
A very commonly requested Excel feature is the ability to add labels to XY chart data points. The XY Chart Labeler adds this feature to Excel. [Used extensively by Jon Peltier]
Excel Utilities
There are several great development utilities available for the VBE, but the Excel Utilities is the only comprehensive add-in aimed at Excel user interface development. The Excel Utilities provides a set of 30 features that you'll use on a daily basis.
VBA Code Cleaner
During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage.
Note: Rob does not write his code to function in Worksheet Modules or in the ThisWorkbook Module. If the code breaks, it becomes unrecoverable. Code Cleaner can “fix” Standard, Form and Class Modules.
Code Documentor
The Excel Code Documentor automatically documents the vital statistics of your VBA project.
Add-in tools from Jan Karel Pieterse ► https://www.jkp-ads.com/
The Name Manager
An excellent utility to manage defined names in your workbooks.
(Incorporated into MS Excel 2007)
Flexfind
Eases searching and replacing throughout an entire workbook. Also enables you to search and replace strings in objects such as headers and footers, chart titles, buttons and many, many more.
Add-in tools from Decision Models Ltd, Charles Williams & Jan Karel Pieterse
► https://www.decisionmodels.com/fastexcel.htm
FastExcel (Cost $79)
"Most slow-running spreadsheets contain a small number of problem areas, or Bottlenecks. Because Excel is such a flexible spreadsheet system there are usually many different formulas that can produce the answer you want. Some of these formulas are much faster than others.
In large spreadsheets it can be difficult to locate and prioritise the Bottlenecks.You can use the wide variety of timing and profiling tools in FastExcel to rapidly drill down, locate and prioritise these bottlenecks, and then use FastExcel's built-in Help for advice on eliminating or optimising them."
Add-in tools from Andy Pope ► https://www.andypope.info/
Button Editor
An add-in for creating button images.
Graphics Exporter
Add-in for exporting items in your workbook to graphics files
Add-in tools from Bill Manville ► https://www.oaltd.co.uk/MVP/Default.htm
FindLink
To look for links from a workbook
Add-in tools from MZ-Tools ► https://www.mztools.com/v3/mztools3.htm
MZToolsVBA
MZ-Tools 3.0 is a freeware 'all-in-one' add-in for Visual Basic 6.0, Visual Basic 5.0 and the Visual Basic For Applications editor (provided by a VBA-enabled application such as those in Office 2000 and higher) which adds several productivity features to
Program from TechSmith ► https://www.techsmith.com/
SnagIt Screen Capture (Cost $39.95)
Using SnagIt, you can select and capture anything on your screen and share it with others to visually clarify difficult concepts, create clearer documentation, and give your presentations a professional, dynamic look.
This is by no means an all-inclusive list of sources or tools, but I hope it will be a start.
Joe Adams
Process Excellence Architect
** Company name removed **
(near) Indianapolis, Indiana
MS Excel is an extremely versatile and powerful tool. You can even develop .xll (similar to .xls files) functions in C+ /C++ and integrate them directly into Excel. This process is used on Wall Street to value and assess Derivative Trading. While we will probably never obtain that level of expertise, we can greatly improve our effectiveness and efficiency along the way.
Websites
The most important web site to bookmark is a listing of Excel’s Most Valuable Programmers. These are independent Excel developers who do not work for MS, but make their living developing Excel applications. They have proven themselves in their knowledge and their willingness to share it.
► https://www.mvps.org/links.html#Excel
From that list the best “General Interest How-to Sites” (especially for new learners) are:
► https://www.cpearson.com/
► https://www.contextures.com/tiptech.html
► https://www.j-walk.com/ss/
► https://www.mvps.org/dmcritchie/excel/excel.htm
► https://www.exceluser.com/index.htm
► https://www.dailydoseofexcel.com/
The “Higher End How-to Sites” are:
► https://www.appspro.com/ - Rob Bovey
► https://www.oaltd.co.uk/ - Stephen Bullen
Rob and Steven are, in my opinion, the best Excel developers / experts in the world.
► https://www.datapigtechnologies.com
► https://www.jkp-ads.com/
► https://www.tushar-mehta.com/
The outstanding “Graphing and Charting How-to Sites” are:
► https://www.peltiertech.com/
Jon is a metallurgist by training, with a Doctor of Science degree from MIT. He has over 20 years experience in research and development, and more recently in manufacturing and production support, in the aerospace, automotive, and industrial parts industries. His background includes Statistics, Total Quality, and Six Sigma. He has developed many quality charts and has posted the how-tos on his web site.
► https://www.andypope.info/
Tip for searching the Web: How do you get a good response to your web search? When you are seeking a “how-do-I” start your search with "MVP", enter your key words that you are searching, and then finish with “*excel*”. By doing this your responses will have an answer from an Excel MVP which will be correct.
Books
The following books are targeted for Excel 2000-2003. The same authors have written companion books for Excel 2007. The books are listed in difficulty order – low to high.
[See the attached Word document or .jpg files for the book images.]
Add-In tools
These Add-in tools can greatly improve your productivity. And the best part is they are FREE.
Rob Bovey once told me, “If you find a tool that you need and they are charging for it, keep searching. There will be a better one free of charge.”
PopTools ► https://www.cse.csiro.au/poptools/
Greg Hood wrote the statistic tool add-in PopTools. MS Excel’s statistical functions are not noted for their robustness and accuracy. If you require the best in statistical tools, add PopTools to your Excel. It has been written as an Add-in so that you can see his methodology and as a compiled for fast execution (a ,xll).
[From the web site]
PopTools is a versatile add-in for PC versions of Microsoft Excel that facilitates analysis of matrix population models and simulation of stochastic processes. Included in PopTools are routines for iterating spreadsheets. These make it possible to run Monte Carlo simulations, conduct randomisation tests (including the Mantel test) and calculate bootstrap statistics. The routines include array formulas for matrix decompositions (Cholesky, QR, singular values, LU), eigenanalysis (eigenvalues and real eigenvectors of square matrices) and formulas for generation of random variables (eg, Normal, binomial, gamma, exponential, Poisson, logNormal). Many of these functions depend on Jean DeBord's TPMath library - a Pascal numerical library - which has been compiled into a DLL accessed via Excel.
Add-in tools from Rob Bovey ► https://www.appspro.com/
XY Chart Labeler
A very commonly requested Excel feature is the ability to add labels to XY chart data points. The XY Chart Labeler adds this feature to Excel. [Used extensively by Jon Peltier]
Excel Utilities
There are several great development utilities available for the VBE, but the Excel Utilities is the only comprehensive add-in aimed at Excel user interface development. The Excel Utilities provides a set of 30 features that you'll use on a daily basis.
VBA Code Cleaner
During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage.
Note: Rob does not write his code to function in Worksheet Modules or in the ThisWorkbook Module. If the code breaks, it becomes unrecoverable. Code Cleaner can “fix” Standard, Form and Class Modules.
Code Documentor
The Excel Code Documentor automatically documents the vital statistics of your VBA project.
Add-in tools from Jan Karel Pieterse ► https://www.jkp-ads.com/
The Name Manager
An excellent utility to manage defined names in your workbooks.
(Incorporated into MS Excel 2007)
Flexfind
Eases searching and replacing throughout an entire workbook. Also enables you to search and replace strings in objects such as headers and footers, chart titles, buttons and many, many more.
Add-in tools from Decision Models Ltd, Charles Williams & Jan Karel Pieterse
► https://www.decisionmodels.com/fastexcel.htm
FastExcel (Cost $79)
"Most slow-running spreadsheets contain a small number of problem areas, or Bottlenecks. Because Excel is such a flexible spreadsheet system there are usually many different formulas that can produce the answer you want. Some of these formulas are much faster than others.
In large spreadsheets it can be difficult to locate and prioritise the Bottlenecks.You can use the wide variety of timing and profiling tools in FastExcel to rapidly drill down, locate and prioritise these bottlenecks, and then use FastExcel's built-in Help for advice on eliminating or optimising them."
Add-in tools from Andy Pope ► https://www.andypope.info/
Button Editor
An add-in for creating button images.
Graphics Exporter
Add-in for exporting items in your workbook to graphics files
Add-in tools from Bill Manville ► https://www.oaltd.co.uk/MVP/Default.htm
FindLink
To look for links from a workbook
Add-in tools from MZ-Tools ► https://www.mztools.com/v3/mztools3.htm
MZToolsVBA
MZ-Tools 3.0 is a freeware 'all-in-one' add-in for Visual Basic 6.0, Visual Basic 5.0 and the Visual Basic For Applications editor (provided by a VBA-enabled application such as those in Office 2000 and higher) which adds several productivity features to
Program from TechSmith ► https://www.techsmith.com/
SnagIt Screen Capture (Cost $39.95)
Using SnagIt, you can select and capture anything on your screen and share it with others to visually clarify difficult concepts, create clearer documentation, and give your presentations a professional, dynamic look.
This is by no means an all-inclusive list of sources or tools, but I hope it will be a start.
Joe Adams
Process Excellence Architect
** Company name removed **
(near) Indianapolis, Indiana
Attachments
Last edited by a moderator:
