Tag Archives: UDF

The meaning of = in Python

In VBA, if you have an array named a and a variant named b, then the statement “b = a” creates a new array b with the same size and values as a.  If the values of either a or b are subsequently changed, the values of the … Continue reading

Posted in Arrays, Excel, Link to Python, NumPy and SciPy, UDFs, VBA | Tagged , , , , , , , , , | 2 Comments

ConBeamU 4.13

The continuous beam analysis spreadsheet, ConBeamU, is now up to version 4.13 with some minor bug-fixes, and the addition of a non-linear option for the BeamAct functions. See ConBeamU for more details of the functions available in the spreadsheet. Download … Continue reading

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

Weighted Least Squares Regression, using Excel, VBA, Alglib and Python

Least squares linear regression in Excel is easy.  That’s what the Linest and Trend functions do.  That is, they find the coefficients of a straight line (or higher dimension shape) so that the sum of the squares of the distances of … Continue reading

Posted in AlgLib, Curve fitting, Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA | Tagged , , , , , , | Leave a comment

Debugging with xlwings and PyCharm

The xlwings documentation covers de-bugging, but it is very brief and it took me some time to get everything working, so this article covers the process in a bit more detail.  The details are affected by the editor being used, … Continue reading

Posted in Excel, Link to Python, UDFs, VBA | Tagged , , , , , , | Leave a comment

Nearest Lookup Function

The Excel Lookup functions (including VLookup, HLookup and Match) all allow for an “exact” or “closest” match on numerical data, but the closest option has a number of problems: The data must be sorted For VLookup and HLookup the data … Continue reading

Posted in Coordinate Geometry, Excel, Maths, UDFs, VBA | Tagged , , , , , , , , | 2 Comments

xlwSciPy 1.09 – update for xlwings 0.10 and Scipy 0.18.1

The xlwSciPy spreadsheet (last presented here) has been updated for the latest version of xlwings and Scipy. The new spreadsheet can be downloaded from: xlScipy-xlw.zip including full open source code. The spreadsheet requires Python, including xlwings, Numpy, Scipy and Pandas … Continue reading

Posted in Arrays, Curve fitting, Excel, Link to Python, Maths, NumPy and SciPy, Python Pandas, UDFs, VBA | Tagged , , , , , , , , | Leave a comment

VBA routines for splitting and joining text

As mentioned in the previous post, I have written two short VBA routines to aid the process of splitting a column of text strings into separate columns, using either a space or any other chosen character as the delimiter.  These … Continue reading

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