A recent comment reported a problem with the use of the UCase and LCase (upper and lower case) functions in VBA. The function was returning the error message “Compile error: Can’t find project or library”.
I found the same problem reported at: http://answers.microsoft.com/en-us/office/forum/office_2007-customize/ucase-problem/2a170fd6-b594-48aa-972f-cdf05a05b99b , which gave the simple solution of adding “VBA.” before UCase or LCase. It seems that the problem only occurs on some computers, and all instances I have seen reported have been with VBA routines that link to compiled dll files.
I have now updated my two spreadsheets where I know this problem has occurred. The new files can be downloaded from:
Anyone having a similar problem with any of my other download files, please leave a comment here.
The Glob_to_Loc function (see Converting from global to local coordinates (and vice versa) ) converts forces and deflections of a beam from the global coordinate system to the local system, defined by the longitudinal axis of the beam and a rotation angle from the horizontal plane. The rotation angle is required because a beam defined by its two end nodes has no defined rotation, but an alternative is to define the beam orientation with three nodes; i.e. the two end nodes and a third node lying on the local x-y plane. This approach is also used for plate-shell elements, where the plane of the element is defined by the coordinates of the corner nodes.
To work with elements defined by 3 nodes I have added two new user defined functions (UDFs) to the IP2 spreadsheet: Glob_to_Loc3 and Loc_to_Glob3. The new version of the spreadsheet may be downloaded from: IP2.ZIP - including full open source code. The download zip file also includes a Python version of these functions. Use of the Python versions requires the PyXll add-in. See Installing Python, Scipy and Pyxll for more details. Details of usage are shown in the screen-shot below (also included in the download file).:
Glob_to_Loc3 and Loc_to_Glob3 functions
The example below shows input for a trapezoidal plate element, followed by results from the FEA program Strand7:
For a 3 Node beam element, using the Strand7 definition of the beam principal axes, use the Axtype = 3 option as shown below. In this case the beam orientation is defined by the 3 node coordinates, with an additional rotation of 30 degrees.
Posted in Coordinate Geometry, Excel, Finite Element Analysis, Frame Analysis, Link to Python, Maths, Newton, Strand7, UDFs, VBA
Tagged UDF, VBA, Excel, IP2.xls, Python, PyXLL, Glob_to_Loc3, Loc_to_Glob3
I posted a VBA function to return The angle between two vectors, in 2D or 3D last year, and have just discovered that Python and Numpy are lacking this function. Since all the suggested code I found in a quick search used:
Cos θ = (a.b)/(|a||b|)
which gives inaccurate results for small angles, I have written my own, using the same procedure as the VBA version:
Tan θ = |(axb)|/ (a.b)
Here is the lengthy code:
import numpy as np
import numpy.linalg as la
@xl_func("numpy_row v1, numpy_row v2: float")
def py_ang(v1, v2):
""" Returns the angle in radians between vectors 'v1' and 'v2' """
cosang = np.dot(v1, v2)
sinang = la.norm(np.cross(v1, v2))
return np.arctan2(sinang, cosang)
For details on how to link to Python functions from Excel, using Pyxll, see: Installing Python, Scipy and Pyxll ; also an updated Glob_to_Loc function, using the py_ang function, will appear within the next few days.
The Frame4Buckle spreadsheet carries out a buckling analysis of a column or strut, which may be divided into any number of segments with varying cross section details, allowing the analysis of stepped or tapered members. The spreadsheet includes a VBA solver which works well for up to about 20 segments, but becomes very slow for 50 or more segments. Also included is the option to call a much faster external compiled solver, using the ALGLIB library, but this requires the installation of two additional interface files (ALMatrixLib.tlb and ALMatrixLib.dll), and the AlgLib dll file (Alglibnet2.dll). These files are now included in the download zip file: Frame4Buckle.zip, and detailed step by step installation instructions are given at: Frame4; now with added Alglib. The Alglib solver has been tested on systems with 32 bit and 64 bit windows, but not with 64 bit Excel.
Note that if you have already installed the Alglib files to run the Frame4 spreadsheet it is not necessary to change anything. Also if the Alglib files are not installed, or are not working, the spreadsheet should automatically switch to the VBA solver.
Finally, for those with Python and Pyxll installed, I will soon be posting versions of the Frame4 and Frame4Buckle spreadsheets using Python solvers.
In case of any problems with installing the files or running the spreadsheet, please leave a comment here.
Posted in AlgLib, Computing - general, Finite Element Analysis, Frame Analysis, Link to dll, Newton, VBA
Tagged AlgLib, Exce, Frame4, Frame4Buckle, installing dlls, VBA
Following comments here and here I have added two examples to the ODE Solver spreadsheet showing use of the ODE function to solve systems of differential equations with two or more coupled equations. The new version (including full open source code) may be downloaded from ODESolver.zip.
The first new example is the system of equations for the Lorenz-Attractor. As for the examples given previously, it is necessary to create a short VBA function to evaluate each of the differential equations for given values of the variables and coefficients. This function is called by the Alglib ODE solver (included with the download file) to solve the system of equations for any specified series of steps:
Lorenz Attractor Solver
Results for coefficients as used by Lorenz:
The second example is the classic predator-prey system, where increasing prey numbers are limited by increasing numbers of predators, until the prey animals go into a rapid decline, followed by decline in predators, until prey numbers start to increase again. This example was taken from the document on solution of differential equations at the XNumbers site: ODE Tutorial
Posted in AlgLib, Differential Equations, Excel, Maths, Newton, UDFs, VBA
Tagged AlgLib, Excel, Lorenz Attractor, Predator-Prey, Solution of ODEs, Systems of ODEs, UDF, VBA, XNumbers