On long and short formulas and VBA

A recent thread at Eng-Tips asked the following apparently simple question:

Starting with a string consisting of numbers with a single central group of letters, how can this string be truncated at the end of the letters, so that for instance 3L481 becomes 3L?

This prompted a lengthy discussion, with many twists and turns, and even some useful answers to the original question.

The original question was looking for an on-sheet solution, rather than VBA, and the first working formula is shown below (all 420 characters of it):

A much more practical alternative (in my opinion) is a VBA user defined function (UDF), which requires just the one argument of the cell address. Two examples are shown below:

Public Function LeftToString(InpStr As String) As String
'
'  Removes trailing digits from a string that comprises
'  a mixture of UPPERCASE letters and digits.
'
'  Elaborations might be required for strings that contain:
'      Lower case letters
'      Only letters
'      Characters that are neither digits not letters.
'
Dim L As Long           'Length of input string
Dim i As Long           'General purpose integer

L = Len(InpStr)
If L <= 0 Then
    LeftToString = ""
    Exit Function
End If
'
'  Loop backwards from the input string's end until hit an uppercase letter.
'
For i = L To 1 Step -1
   If UCase(Mid(InpStr, i, 1)) >= "A" And UCase(Mid(InpStr, i, 1)) <= "Z" Then
        LeftToString = Left(InpStr, i)
        Exit Function
    End If
Next i
'
' Input string contains no letters.
'
LeftToString = InpStr
End Function

And a shorter version:

Function LeftToString2(X As String) As String
Dim i As Long

For i = Len(X) To 1 Step -1
    If (InStr("0123456789", Mid(X, i, 1))) = 0 Then
        LeftToString2 = Left(X, i)
        Exit Function
    End If
Next i
End Function

Results of the two UDFs are shown in the screen-shot above. Note that the first UDF checks for upper-case text, between A and Z, so the extracted character must be converted to upper case. The second avoids the problem by checking if the character is a number.

A much shorter on-sheet formula was then supplied:

Even with the shorter formula, it is not immediately obvious how it works, so I have split it up into its constituent parts. With the input string in Cell B30:

  • =RIGHT(B30,ROW(INDIRECT(“1:”&LEN(B30)))) returns an array of progressively longer strings, starting from the right hand end.
  • =VALUE() converts each member of that array either into a a number or #VALUE!.
  • =ISNUMBER() returns TRUE or FALSE for each of those values.
  • =SUM(–array) or =SUM(array*1) returns the number of TRUE values. The — or *1 operators are required for the SUM function to treat TRUE as a value of 1, otherwise the SUM will always return 0
  • Finally =LEFT(B30, LEN(B30)-K30) extracts the string up to the last text character.

In the latest version of Excel with “dynamic arrays” the second function will work when entered with the enter key, as usual. In older versions it must be entered as an array function, by pressing Ctrl-Shift-Enter.

Even with the second simpler formula, to my mind the VBA function is the better alternative, both in terms of application in a new spreadsheet, and understanding how the thing works. For those not familiar with VBA, the process of creating a new function is quite simple:

  • Open a new spreadsheet (or an existing one you want to use the function in) and save with a chosen name.
  • Press Alt-F11 to open the VBA editor.
  • Right-click on VBAProject(spreadsheet name) in the list of open files on the left, and Insert-Module (see screenshot below)
  • Copy the VBA code and paste it in the new module.

Finally I recommend having a look at the full EngTips thread linked above for discussion on a variety of topics, including whether to declare integer variables as Integer or as Long, how the first long formula works, the quick way to review long formulas, without splitting them up into parts, and VBA code for highlighting the active cell.

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

ULS Design Functions 2.13

I have uploaded another update to the ULS Design Functions spreadsheet with some new features and fixes.  The new version can be downloaded from:

ULS Design Functions.zip

The changes in the new version are:

  • Stress block type 3 will now use the Eurocode full parabolic stress block.
  • In previous versions entering a concrete yield stress of 600 MPa resulted in a divide by zero error. This has now been fixed.
  • Using the AS 3600 rectangular stress block, there has been a minor correction to the calculation of section capacity when the neutral axis is at or beyond the “tension” face, so that linear interpolation is used, as required by the code.

The Eurocode parabolic stress block may be used with AS 3600 and AS 5100, as well as Eurocode 2:

Interaction diagrams to AS 3600 are shown below for the rectangular stress block (including 10% reduction in concrete stress for a non-rectangular section, the Eurocode parabolic-rectangular stress block, and the full parabolic stress block:

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

… and something a bit more recent

Castlereagh Connection

A young band from Coonamble aged 11-13, they’ve been performing together for over 4 years

11 July 21: For some reason the Facebook video link isn’t working any more, so here is another one from Youtube:

Posted in Bach | Tagged , | Leave a comment

Imagine …

was recorded 50 years ago …

Posted in Bach | Tagged , | Leave a comment

Making Finite Element Analysis go faster …

… with Numba and Scipy sparse matrix functions.

As promised in the previous post in this series, I will be looking at speeding up the formation and solving of matrix functions for the analysis of structural frames, using Excel and pyxll to call Python routines with the Numba jit compiler, and Scipy sparse matrices and solvers.

For an example I have used the 3D analysis of a concrete building frame with 7065 beams and 2730 nodes. This has 14,742 degrees of freedom, which if analysed using the complete stiffness matrix would require creating and solving a matrix with 217,326,564 elements. The actual matrix has only 176,904 non-zero elements, so the use of a matrix in sparse format saves an enormous amount of memory resources and computing time. The use of alternative Scipy solvers, and options within each solver, also made a huge difference to the solution time.

The table below shows times for reading the data from Excel and setting up the stiffness matrix:

The time saving from using Numba is fairly modest (presumably because most of the work involves manipulating data within Numpy arrays), nonetheless the creation of the final stiffness array shows a useful time saving when using Numba.

I have used two matrix solver routines from the Scipy linalg and sparse.linalg libraries, and each of the 9 alternative iterative sparse solvers:

  • solveh_banded: Solve equation a x = b. a is Hermitian positive-definite banded matrix.
  • spsolve: Solve the sparse linear system Ax=b, where b may be a vector or a matrix.
  • Iterative solvers:
  • bicg: Use BIConjugate Gradient iteration to solve Ax = b.
  • bicgstab: Use BIConjugate Gradient STABilized iteration to solve Ax = b.
  • cg: Use Conjugate Gradient iteration to solve Ax = b.
  • cgs: Use Conjugate Gradient Squared iteration to solve Ax = b.
  • gmres: Use Generalized Minimal RESidual iteration to solve Ax = b.
  • lgmres: Solve a matrix equation using the LGMRES algorithm
  • minres: Use MINimum RESidual iteration to solve Ax=b
  • qmr: Use Quasi-Minimal Residual iteration to solve Ax = b.
  • gcrotmk: Solve a matrix equation using flexible GCROT(m,k) algorithm.

For all cases the initial “A” matrix (i.e. the square stiffness matrix) is prepared in COO format, which allows the matrix to be compiled with the minimum of calculation. The COO format consists of 3 columns listing each non-zero value of the array, and the row and column number of each value.

The solveh_banded function requires the A array to be a Hermitian banded array. Scipy does not seem to have a function to convert from COO to banded, so I wrote one:

def COO2BandedVect(kv, ki, kj):
    """
 Convert a COO matrix to symmetric banded upper triangle format.
 kv ki kj: COO matrix vectors
    """
    rows = kv.shape[0]
    n1 = 0
    cols = 0
    for k in range(0, rows):
        i = int(ki[k])
        j = int(kj[k])
        if i > cols: cols = i
        if (i-j) > n1: 
            n1= i-j
    cols = cols+1
    bandwidth = n1+1
    
    BandMat= np.zeros((bandwidth, cols))
    for k in range(0, rows):
        i = int(ki[k])
        j = int(kj[k])
        if i - j <= 0: BandMat[ i + (n1-j), j] = BandMat[i + (n1-j), j] + kv[k]
    return BandMat   

For the other functions the three vectors, v, i and j, must be converted to a COO matrix then to csc or csr format, which may de done with a single line of code:

 A = ssp.coo_matrix((v,(i,j)),shape=(n,n)).tocsc()

The solveh_banded function performed much better than the other analytic solver (spsolve) with default arguments, with solution times of 0.70 and 18.15 seconds respectively. Spsolve has an optional argument called permc_spec that defines “how to permute the columns of the matrix for sparsity preservation”. The options with solution times are shown below:

For this case the “Natural” option is thus almost 6 times faster than the default “Colamd” option, and 11 times faster than the slowest option! The solveh_banded function, with default arguments, however remains substantially faster.

The 9 iterative solvers also had a huge range of performance, with solution times ranging from 0.5 seconds to 69 seconds. The gmres solver was by far the slowest, and gcrotmk was also very slow. The cgs solver returned an error. The performance of the other 6 is shown in the graphs below, with the first plotting solution time against the input tolerance value (log scale), and the second solution time against maximum error, compared with the results from the analytic solvers.

The minres solver was substantially faster than the others, but also generated greater errors with the default tolerance. The errors from the other solvers were all close to the minumum when run with the default tolerance of 1E-08. To reduce the minres error to this level it was necessary to reduce the specified tolerance to 1E-11. With this reduced tolerance minres remained the fastest of the iterative solvers, with a solution time of 0.75 seconds.

In Summary:

  • The banded solver (solveh_banded) was the fastest overall for high precision results, when run with default input.
  • The spsolve function performed much better with the permc_spec argument set to “NATURAL”, rather than the default, but was still substantially slower than solveh_banded.
  • The minres function was the fastest of the iterative solvers, but required the tolerance value to be set to 1E-11 to minimise the maximum error in the results.
  • All results are based on solving one large, very sparse matrix. Different problems are likely to have different functions giving the best results.
Posted in Arrays, Excel, Finite Element Analysis, Frame Analysis, Link to Python, NumPy and SciPy, PyXLL | Tagged , , , , , , , , | 1 Comment