Tag Archives: VBA

New sort functions and sorting to groups

Posted on May 16, 2020 by

I first posted a user defined function (UDF) to sort spreadsheet or VBA data back in 2009.  Finally Microsoft have caught up, and Excel 365 subscribers now have two built in sort functions available: =SORT(array,[sort_index],[sort_order],[by_col]) =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…) … Continue reading →

Posted in Excel, UDFs, VBA | Tagged , , , , , | Leave a comment

LinEstGap with non-linear functions

Posted on March 14, 2020 by

The LinEstGap user defined function (UDF) allows the built-in Linest function to be used on data with empty cells or hidden rows, returning results that agree with the chart trend-line function, which automatically ignores blank cells and hidden data.  It … Continue reading →

Posted in Arrays, Curve fitting, Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , , , , | 1 Comment

DigitGraph Spreadsheet and WebPlot Digitiser

Posted on March 3, 2020 by

I have posted here previously a spreadsheet that allows XY data to be extracted from images of graphs, maps or other images of objects in a single plane: How to digitise a scanned image. I have updated the instructions for the … Continue reading →

Posted in Charts, Charts, Computing - general, Curve fitting, Drawing, Excel, UDFs, VBA | Tagged , , , , , | 5 Comments

Asymmetric Catenary Function

Posted on January 31, 2020 by

Back in 2009, I posted a user defined function (UDF) to generate a catenary curve, that could be used together with the Excel Solver to generate a curve of a specified span and sag: A catenary function Following a discussion … Continue reading →

Posted in Coordinate Geometry, Curve fitting, Excel, Maths, Newton, UDFs, xlwings | Tagged , , , , | Leave a comment

Using Excel Solver from VBA

Posted on January 28, 2020 by

Using the “record macro” icon in Excel to record the solver process, I had a couple of problems: When I tried to run the recorded macro, I got the message “Compile Error – Sub or Function not defined”. When that … Continue reading →

Posted in Excel, VBA | Tagged , , , , | 1 Comment

Column buckling under multiple loads

Posted on January 20, 2020 by

Following another buckling discussion at Eng-tips I have modified my Frame4-buckle spreadsheet to allow input of multiple axial loads at any point along the column, along with any number of sections with different section properties.  Background on the method used … Continue reading →

Posted in AlgLib, Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Newton, UDFs, VBA | Tagged , , , , , , , , , | Leave a comment

Python Traps

Posted on January 1, 2020 by

When converting existing VBA code to Python there are a number of obvious changes to the syntax that need to be made for the code to run at all, but there are also a number of not so obvious traps … Continue reading →

Posted in Arrays, Excel, Link to Python, Newton, NumPy and SciPy, PyXLL, UDFs, VBA | Tagged , , , , , , , , | 1 Comment