Copying charts to a new workbook

When an Excel worksheet including a chart(s) is copied to a new workbook the chart still links to the data ranges in the original workbook. Over the years I have spent a fair bit of time editing the chart ranges to their intended location in the new workbook, but recently I decided to check if there is a better way, and found:

Copy Chart to New Sheet and Link to Data on New Sheet at Jon Peltier’s blog

The procedure is:

  • Right click on the tab of the worksheet to be copied
  • Select “Move or Copy …” then in the dialog box select “New book” under “To book”, and select the “Create a copy” check box
  • Click OK
  • The worksheet will be copied to a new file (called book1.xlsx), including any charts, with the charts linking to the data in the new file.
  • Save the new file with a new name, remembering to save as .xlsb or .xlsm if you want to add any VBA code.

Note that the worksheet may also be copied to an existing file, or to a new position in its current file. Also it is possible to copy more than 1 worksheet by selecting the sheets to be copied before right-clicking on one of the selected tabs.

Copying the worksheet:

The resulting new workbook, with chart linked to the data in the new file:

Posted in Charts, Charts, Excel | Tagged , , | Leave a comment

A Numpy trap – correction

In my post of 30th May this year (here) I said that:

As a check that the functions were working correctly, the Python functions were modified to return the sum of the largest array in the first row, revealing that the Numpy code was returning the wrong results!  …

It seems that the Numpy arange function uses 32 bit integers, even if the range extends outside the 32 bit range! 

That’s not quite right. In fact the range of Numpy 32 bit integers is -2,147,483,648 to 2,147,483,647 (which is the same as VBA Longs), and the largest value generated by the quoted arange function was only 100,000. The code generating an incorrect result (without generating an error message) was:

@xl_func
def numpysumn(k):
    a = np.arange(k)
    return a.sum()

With a k value of 100,000 the Numpy arange function generates a sequence from 1 to 99,000, so there is no problem generating the array, but the sum of the array members is 4,995,000,000, which exceeds the 32 bit integer limit.

Alternative solutions to this problem are:

  • Declare the arange function as a 64 bit integer:
    a = np.arange(k, dtype=np.int64)
  • Declare k as a 64 bit integer:
    k = np.int64(k)

In both cases the array a will have a 64 bit integer datatype, and a.sum will return the correct result.

Posted in Arrays, Excel, Link to Python, NumPy and SciPy, PyXLL, UDFs | Tagged , , , , , | Leave a comment

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