Custom Excel Macros | Excel Spreadsheet Training

By: Lucas Technical Services  09-12-2011

Most people don’t know that there is a very powerful computer language sitting in the background of their Excel spreadsheets.  It is called VB (Visual Basic), and it can be used to write custom macros that can do, well, just about anything.  A well-written VB macro can automate any manual process that you can do in excel, but it can do far more also.  LTS has made countless programs that look and act like stand alone, commercial software that serves a variety of our client’s needs.  Anywhere from database manipulation, advanced engineering design programs, test data number crunching, add-in functional utilities.  We have never come across an Excel challenge that VB could not offer a clean and efficient solution.

The experts at LTS are masters of the VB environment, and we pride ourselves on using Excel’s functionality to its maximum potential.  If you have a time-wasting process involving number crunching, data manipulation, financial reporting, you NEED to talk to LTS and find out how we can  increase your productivity, and let the software work for you.

Corporate and Individual Excel Training

LTS will train individuals or corporate groups.  Every training session is individualized to  meet the trainee’s experience level and goals.  The general categories include:

  • Beginner Excel
  • Intermediate Excel
  • Advanced Excel
  • Intro to Visual Basic
  • Intermediate Visual Basic
  • Advanced Visual Basic

Technical Matters

There are many folks out there that dabble with VB, but LTS offers unmatched technical prowess with the fine points of VB and Excel.  We know the capabilities and limitations, which is important when you roll your sleeves up and start writing code.  Here is an example of what we can do with Excel and VB that others can’t:

Polynomial Regression

If you’ve ever tried to perform a polynomial curve fit to plotted data, you’ve probably found that Excel is not very cooperative in providing the trendline constants for use in the sheet.  The novice solution is to display the equation on the chart, and manually type the constants into cells that you can then use to predict other results based on the data.  This is a poor technique, because if your source data changes, the constants need to be manually updated.

The intermediate technique is to use the LINEST() function in excel.  Some power uses know that LINEST can be manipulated to return regression statistics and coefficients of not only a linear regression, but higher-order polynomial regressions as well, and it returns them in an array-formula format.  This is a pretty slick technique, and it was perfect in Excel 2002/2003, but in Excel 2007/2010, the functionality got severely diminished.  Before, you could reliably perform up to a 6th order or higher regression.  But in the newest version of Excel, LINEST is for lack of a better term, broke.  If you try to do higher than a 2nd order regression, it outputs garbage data.

An advanced technique is to do a manual regression calculation in the sheet itself…taking the sum of power terms and product terms of your X & Y data, constructing matrices in the sheet, and performing matrix inversions and multiplications using the MINVERSE() and MMULT() functions.  This technique works ok, except that it takes up alot of space on the sheet, and is difficult to modify the regressed order.  And there is one more pretty important flaw, and that is the limitations of the embedded matrix math functions of excel…the only calculate to 15 significant digits.  This means, that any regression higher than a 4th or 5th order fit starts to produce fairly large error, especially if your data itself is very large or very small.

So what do we do?…well the LTS solution (or better called the super-advanced technique) is to make a custom function that does all the math and avoids any of the embedded Excel function limitations.  LTS has a very good custom function that can be added to your Personal Macro Workbook that has virtually unlimited  polynomial regression capability.  The way it works is basically a VB automated process of finding the sum-of-squares terms, creating matrices in array forms, and performing a manual matrix inversion and matrix multiplication, taking advantage of the double-precision data type in VB.

There are only a handful of power-users that will really even grasp what these differences are; but hopefully it proves a point that, when it comes to VB and Excel, at LTS…we know what we are doing.

Contact Lucas Technical Services


Print this page

Other products and services from Lucas Technical Services


CAD Designer and Consultant | 3D CAD Drafting and Design Services

We have a wealth of experience working in both SolidWorks and Unigraphics, and are pleased to offer both depending on your needs and preferences. Some projects require hundreds or thousands of unique parts to be combined in sub and main assemblies, resulting in a singular, cohesive design. A solid model is built out of operations a sketch that results in the creation of a solid body.


Mechanical Engineering Services

LTS will create 3D models of your idea, optimize the design in virtual space, and produce manufacturing drawings to make it real. Whether it’s planetary sets, spur gears, spline drives…you name it, LTS has the solutions for your power transmission needs. Leverage the technical strength of LTS to crunch the numbers and ensure that your idea works right…the 1st time.


New Product Protoyping, Design Engineer and Development Services | Custom Products Designer

LTS can guide you through the entire process, or for just a few of the steps depending on your needs. All new consumer products have to go through some form of the following steps.


Mechanical Engineering Analysis and Consulting | 3d Mechanical Design Analyst

This can be a tricky matter, but LTS uses state-of-the-art, custom designed engineering tools to manage all of the tolerances to ensure that everything will fit together as intended. This tolerance has to be considered when designing mating parts…the Min diameter for the hole must not interfere with the Max diameter of the pin.


Planetary Gear Design and Optimization

Gear ratio selection tool- Multiple planetary stage design- Non-standard involute profile optimization - Stress and contact ratio analysis- Gear meshing animation- Automatic gear block creation- Gear design database utility- Automated gear design optimization. Most importantly, LTS will make deviations to the standard involute profile, and optimize the design to equalize and minimize teeth stress, as well as maximize contact ratio.