Links on Lists, and other things.

Posted on 31/01/2015 by dougaj4

Daily Dose of Excel recently had a post on interacting with List Objects from VBA.  Since I make very little use of Lists (or Tables as they are now known in Excel, but not VBA), I thought I’d have a search for more information.  There doesn’t seem to be a huge amount of information available, but a good starting point would be:

Working with Tables in Excel 2013, 2010 and 2007 (VBA) from Jan-Karel Pieterse.

See also the first comment below from Winston Snyder, with 6 more List related links.

Other useful links gleaned from DDoE:

EXCELXOR is a relatively new Excel blog with excellent content:

If you’re looking for articles relating to advanced, formula-based Excel solutions then you’ve come to the right place.

Please visit my blog to see a whole host of innovative techniques developed using worksheet formulas alone. These techniques – accompanied by detailed and instructive explanations of their workings – are not only of theoretical interest to the reader wishing to explore the possibilities of what can be achieved in this field, but also offer practical solutions to a plethora of everyday issues which the average Excel user may encounter.

Excellerando has been going for some time.  Not a huge turnover, but over the years it has built up an excellent library of VBA code advice and examples.

Finally another Alfred Vachris link:

VBA Projects by Mark Kubiszyn

Welcome to my blog. This is all about imparting knowledge. I want to share some of the insight and learnings from six years of developing in VBA for Excel. I also want to cover off some innovative techniques, coding best practices and share some of my development Tools & Reports.

Please feel free to download and share my work – you can use the Code and techniques to develop your own VBA Projects. To send me a message, you can contact me on my Homepage

Posted in Computing - general, Excel, VBA | Tagged , , , , | 2 Comments

Simpson’s Donkey

Posted on 27/01/2015 by dougaj4

Simpson and his Donkey.

John Simpson Kirkpatrick

Posted in Bach | Tagged , , , | 2 Comments

Smart Indenter and Excel 2013

Posted on 27/01/2015 by dougaj4

I have recently installed Office 365 on a new computer running Windows 8.1 (actually the third one in just over a month, but that’s another story).  Office 2013 has several annoying features, but for me the most annoying was that the VBA smart indenter add-in from Office Automation did not work.  The web-site only mentions Excel versions up to 2003, but it was working with no problems with Office 365 and Excel 2013 on my last Windows 7 computer, and a search suggests that it should work with Windows 8 and 32 bit versions of Office 2013, but on my machine it doesn’t.

My solution to this problem was to install the alternative smart indenter from Andrew Engwirda.  This works on my system with no problems (after reading the installation instructions, you need to enable “Trust access to the VBA project object model” ), and will also work with 64 bit versions of Office.

If anyone else has had a problem with the original code indenter and 32 bit Office, I’d be glad to hear from you, especially if you found a way to make it work, but in the meantime Andrew’s alternative code works just fine.

Posted in Computing - general, Excel, VBA | Tagged , , , , , | 26 Comments

Finding Excel Version Information

Posted on 26/01/2015 by dougaj4

The information below was buried in an old post, but I had trouble finding it when I wanted it, so here it is in its own post:

Finding the version information in Office used to be easy; it was always under Help-About, but Microsoft has apparently decided that this information might frighten the uninformed, so they have decided to hide it:

  • In Excel 2010 it is under File-Help
  • In Excel 2013 it is under File-Account-About Excel
Posted in Computing - general, Excel | Tagged , , | 1 Comment

Numerical Integration; Tanh-Sinh Quadrature v. 4.3

Posted on 29/12/2014 by dougaj4

A new version (4.3) of the numerical integration spreadsheet by Graeme Dennes is now available for download from Tanh_Sinh Quadrature.

The Tanh-Sinh Quadrature Version 4.3 workbook now provides a feature for the integration of integrands contained in independent (external) UDFs. By using this feature, changes to the workbook VBA programs are not required, providing a simplification of the use of the workbook. Although the performance shown by this method is not as great as that shown by the VBA-stored method, it is faster than the cell-stored method, and adds to the utility and functionality of the workbook without requiring VBA changes to be made by the user. All 13 quadrature programs can utilise this new feature. Added examples and documentation for using this new feature have been included on the T-S EXAMPLES worksheet. This feature was proposed by Doug Jenkins.

Also included in this release is the ability to evaluate text as a formula, further adding to the functionality of the workbook. Also proposed by Doug.

My thanks to Doug for raising these changes to enhance the workbook.

Graeme Dennes

In addition to the main workbook, containing full documentation and numerous examples, two other workbooks are now included in the download zip file, to simplify the process of using these functions in other applications:

  • Tanh-Sinh Quadrature v4.3 by Graeme Dennes-Code Only.xlsm: includes full open-source VBA code, but documentation is reduced to 2 simple examples of each function.
  • Tanh-Sinh Quadrature v4.3 by Graeme Dennes-Minimal Code.xlsm: includes code for the Tanh-Sinh function only, with two examples of using this function to evaluate integrals required by other routines.

Either of the cut-down workbooks may be incorporated in other applications by either inserting new worksheets and VBA code as required, or by copying the VBA code modules to another application.  VBA modules may be copied between projects by opening both workbooks, then simply selecting the desired modules in the VBE Project Explorer window, and dragging them to the new project “Modules” folder.

Alternatively the workbooks may be saved as an add-in (open the Save-as window, then select “Excel add-in” (*.xlam)), so that all the functions will be available from any open workbook.

Posted in Excel, Maths, Newton, Numerical integration, UDFs, VBA | Tagged , , , , , , , , | 6 Comments