VBA Read Unicode file contents in various encodings

To read a text file, you need to be able to handle more character sets than just ANSI. Not just in the contents but also in the file and folder names.
The classic article on this is by Joel Spolsky:
https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/

Here are folder and file names in various languages:

F:\DOCS\Arabic\التاريخ المالي والنسب.xls
F:\DOCS\Arabic\اسم ملف الاختبار\اسم ملف الاختبار.xlsm
F:\DOCS\Chinese\2016 全 日 制 ⑴ 班 2016 ～ 2017 学 年 度 第 一 学 期 MBA 教 学 计 划（教室：2-111）.xls
F:\DOCS\Chinese\測試文件名\測試文件名.xlsx
F:\DOCS\Greek\όνομα αρχείου δοκιμής\όνομα αρχείου δοκιμής.xlsx
F:\DOCS\Hebrew\שם קובץ הבדיקה\שם קובץ הבדיקה.xlsx
F:\DOCS\Hindi\परीक्षण फ़ाइल नाम\परीक्षण फ़ाइल नाम.xlsx
F:\DOCS\Hungarian\kalóriaguru Munkafüzet.xlsm
F:\DOCS\Hungarian\kalóriaguru\kalóriaguru.cls
F:\DOCS\Japanese\テストファイル名\テストファイル名.xlsx
F:\DOCS\Korean\테스트 파일 이름\테스트 파일 이름.xlsx
F:\DOCS\Russian\Лист1.xlsx
F:\DOCS\Russian\имя тестового файла\имя тестового файла.xlsx

Misinterpreting characters results in accented characters appearing as two garbage characters; or you see a white question mark in a black diamond which indicates that UTF-8 encoding could not figure out what it was.
https://en.wikipedia.org/wiki/Mojibake

In the case of VBA, the classic Open statement, and the functions Dir(), Filelen(), FileDateTime() cannot handle Unicode file names and folders. The Filesystem Stream object can read Unicode names but cannot handle UTF-8 encoding. So, I use the ADO.Stream object in the module ReadFileContents.bas to handle a number of different cases. The variations are not complete but it handles the file types I get.

Let me know of any improvements you can suggest.

Here is the function:

' Reads the entire contents and tries to GUESS the character encoding
Function ReadFileContentsAndCharset(ByVal FileName As String, ByRef Text As String, _
         ByRef charset As String) As Long
   charset = "UTF-8" ' first guess. If it's not UTF-8 it may still be readable as UTF-8
   ReadFileContentsAndCharset = ReadADOStreamText(FileName, Text, -1, charset)
   If ReadFileContentsAndCharset = 0 Then ' it succeeded without error
      Debug.Print FileName   ' comment these out in production
      Dim lUTF8Len As Long, sBOM As String
      ' UTF-8 files do not have to have a BOM so no point in testing for that
      sBOM = Left$(Text, 2)
      lUTF8Len = Len(Text)
      If sBOM = Chr$(254) & Chr$(255) Or sBOM = Chr$(255) & Chr$(254) _
         Or InStr(1, Text, Chr(0)) > 0 _
         Then ' UTF-16 or nulls? Might be double-byte character set
         charset = "UTF-16"
         ReadFileContentsAndCharset = ReadADOStreamText(FileName, Text, -1, charset)
         Debug.Print charset & " len=" & Len(Text) & ", utf-8 len=" & lUTF8Len
         If lUTF8Len = Len(Text) * 2 Then
            'ReadFSOStreamText(FileName, Text) would work here too for UCS2
            Debug.Print "As expected, double byte character set"
         End If
      ElseIf InStr(1, Text, ChrW$(65533), vbBinaryCompare) > 0 Then ' &HFFFD
         ' Check for U+FFFD <question mark in black diamond> replacement character
         ' used to replace an unknown, unrecognized or unrepresentable character
         'https://en.wikipedia.org/wiki/Specials_(Unicode_block)
         charset = "Windows-1252" ' second guess; superset of ISO-8859-1
         ReadFileContentsAndCharset = ReadADOStreamText(FileName, Text, -1, charset)
         Debug.Print charset & " len=" & Len(Text) & ", utf-8 len=" & lUTF8Len
         If Len(Text) > lUTF8Len Then
            Debug.Print charset & " converted some characters into multiple characters"
            ' so it must be some other encoding. No idea.
         End If
      End If
      Debug.Print Len(Text) & " chars, charset inferred=" & charset & " " & FileName
   Else
      MsgBox FileName & vbLf & "Error " & Err & " " & Err.Description
   End If
End Function

Function ReadADOStreamText(ByVal FileName As String, ByRef Text As String, _
   Optional ByRef numchars As Long, Optional ByRef charset As String) As Long
   ' initialise default parameters
   If numchars = 0 Then numchars = -1
   'numchars Optional. The number of characters to read from the file, _
      or a StreamReadEnum value. Default is adReadAll=-1
   If Len(charset) = 0 Then charset = "utf-8"
   With CreateObject("ADODB.Stream")
      .Type = 2 'adTypeText = 2 Specify stream type -  text/string data.
      .charset = charset
      .Open
      On Error Resume Next
      .LoadFromFile FileName
      'Err  3002         File could not be opened.
      Text = vbNullString ' in case of err
      Text = .ReadText(numchars)
      ReadADOStreamText = Err
   End With
End Function

A look back to Eusprig 2019

The 20^th conference of the European Spreadsheet Risk Interest Group was held in  Browns of Covent Garden, London on 11-12 July 2019,

Thursday 11 July 2019

Developing Excel Thought Leadership

David Lyford-Smith, ICAEW, Chartered Accountants’ Hall

This described the development of their published booklets on Twenty Principles, the Spreadsheet Competency Framework, and Financial Modelling Good Practices.  David summarised their approach as:

• We have a model for developing spreadsheet thought leadership:

1. Identify an area where a high-level guidance publication could be of use by consulting with experts and stakeholders
2. Convene a suitable and diverse group of experts and synthesise a consensus view from their disparate opinions
3. Invite commentary and critique via an exposure draft process
4. Complete the project based on the feedback received

• With such variation in opinions and practice, building in room for individual preference and differences in approach must be an explicit consideration
• Many people are willing to follow good practice when exposed to it, but few will seek it out for themselves

Practical aspects of applying an End User Computing policy

Roger Turner, Wesleyan Assurance Society, Birmingham

This was an update on his 2018 paper where he described the work Wesleyan did to set up its EUC policy. There was a continual focus on meeting and working with managers to be sure that actions were completed. His slides give a very frank account of their journey which will be very valuable to those present working on similar projects. Their fine-grained approach to classifying risks made it easier to score some quick wins, on things like Controls, Validation, and Documentation. Some slides showed their KPIs to measure progress on moving spreadsheets from red to green status. His learning points are:

1. Plan what you’re going to do
   1. If you fail to plan you plan to fail
   2. Work out risk metrics, use best practice to make an EUC policy, do pilot runs to iron out problems.
2. Get buy-in right from the top
   1. Identify the risks and benefits – suggest a risk based approach tackling where the biggest risks are first.
   2. Success with these will give your project momentum which you will need for harder areas
3. Use of expensive software can usually be avoided
   1. Look to re-use existing infrastructure and systems
   2. A top down approach can get some results and is cheaper, less intrusive than a bottom-up approach
4. Publicise the policy with its benefits (intranet, meetings etc to make the approach clear)
5. Be sympathetic with peoples’ other business priorities
   1. Most will try to help and this will give you momentum
6. Use KPI reporting – this will show how your EUC project is reaping benefits over time.

A Case Study of Spreadsheet Use within the Finance and Academic Registry units within a Higher Education Institution

Simon Thorne and Jamie Hancock

This was a survey of the Academic Registry and Finance departments of a university. The paper has many tables giving an analysis of the responses to the survey, although bear in mind that this is self-reporting. They identified the common risks:

1. Lack of training, standards, development methods, testing and documentation in spreadsheet development
2. Mistakes in communicating with third parties
3. Potential for fraud and falsification
4. GDPR risks

The mitigating actions that follow are:

1. Generate an inventory of active critical spreadsheet applications and take appropriate mitigating actions with the riskiest spreadsheets.
2. Development of a robust spreadsheet development policy governing approaches to development, deployment, documentation, testing and accountability
3. As part of the organisational policy, decide on development methodologies, documentation standards and testing policies for spreadsheets
4. Identify and deliver appropriate training in planning, development and testing of spreadsheet models
5. Establish peer working, user groups and risk awareness sessions

Abstracting spreadsheet data flow through hypergraph redrawing

David Birch, Nicolai Stawinoga, Jack Binks, Bruno Nicoletti, Paul Kelly

Imperial College London, Filigree Technologies, London

This represents the spreadsheet as a network diagram of its dependencies. They make the link structure explicit by transforming the spreadsheet into a graph structure. They find groups of cells performing near-identical operations by detecting vector operations using isomorphic subtree detection. They illustrate the effectiveness of raising the abstraction level by translating a series of spreadsheets to a high-level data-flow language called ModL. By applying a force-directed algorithm they can rotate the graph in a virtual 3D space to better see the relationships.

More established technologies such as Quantrix are specifically aimed at the financial modelling market and start with such structures as their design surface.

Implementation Strategies for Multidimensional Spreadsheets

Paul Mireault, Founder, SSMI International, Honorary Professor, HEC Montréal

Last year, the author issued a challenge to spreadsheet users to create a spreadsheet model with some initial data and simple formulas to allocate quantities and money over four dimensions. There was no challenge in the formulas, they were given them all, it was simply a request to implement a model. Even with that, of the 109 who responded, only 17 completed the challenge. Some implemented it as tables which spread the four dimensions over multiple blocks of formulas in multiple worksheets. Others collated the data in a flat database structure and created the reports as simple summaries of the data. His conclusions are:

1. Participants using the Database approach usually have the simplest formulas for aggregated variables. We expect that they will be the easiest to maintain.
2. The analysis of the Multi-dimensional Spreadsheet Challenge submissions has given us insight on the different implementation strategies used by seasoned spreadsheet developers. Given the same problem and its solution, developers had very different results. This lack of standardization may explain the why it is hard to understand and maintain somebody else’s spreadsheet.

EQUS – helping to see formulae

Chris R. Roast, Sheffield Hallam University

EQUS is a C# Excel addin that presents a graphical breakdown of cell formulas annotated with an evaluation of each subexpression and the calculation steps. It is like a more colourful, simultaneous, presentation of what the Formula Evaluation tool in Excel does; or other formula reference tree viewers in the Excel addins market. This is intended for educational use and is very suited to explaining to students how the formulas work.

From webtables to datatables

Mária Csernoch, University of Debrecen, Hungary

The motivation for this work is to create realistic and interesting tables of data for school students to analyse. Web sites are an expected source, but unfortunately many frustrate the ability to conveniently collect data from their pages. Standard web queries depend on data in HTML tables, but many use <div> or other tags which can not be read by Get Data from Web, or Powerquery. She presented the steps they must use for copy/paste or saving pages as HTML or plain text and opening in Excel or Word; then reformatting, editing, cleansing, parsing, and reshaping to get a usable table out of the result. It can be a lot of tedious work. While professional developers may use VBA and APIs, this aims to create reproducible manual steps which schoolchildren can use. Be aware that these are Hungarian schoolchildren who have already been taught Excel array formulas.

Will Dynamic Arrays finally change the way Models are built?

Peter Bartholomew , MDAO Technologies Ltd

The author is an engineer, not a financial modeller. His kind of task for the MoD was a low-fidelity Excel model of the likely interaction of warship and helicopter in the North Atlantic, with a graphical readout of the behaviour. His models are largely constructed with names rather than cell references, so have always been innately array-oriented. He points out that Excel 365 dynamic arrays now provide the most direct route to a solution

instead of requiring effort to set up Control-Shift-Enter (CSE) objects; any formula can be an array calculation, any cell can hold an entire result array.

The surprise for anyone versed in traditional  methods may well be how few cells contain formulas, so consistency errors are simply not possible and having fewer formulas reduces the opportunity for error.

Cubes an alternative to spreadsheets to mitigate risk and improve performance?

Marc BRAUN, Xcubes

This was a demonstration of XCubes, a Multidimensional Spreadsheet System software package. He used the model specified in Paul Mireault’s talk as an illustration. An Excel solution required 1,427 formulas; an XCubes solution just 13. Cubes are the most suitable solution when the model can be broken down in blocks to store well-structured data. They make analysis, calculation and visualization even on large data sets much easier. The solution can be downloaded from xcubes.net.

Does the teaching of spreadsheeting skills have the potential to be a significant global education policy intervention?

Morten Siersted, Visiting Research Fellow, Centre for Development Studies, University of Bath

Spreadsheeting skills do not currently feature within global education policy in any significant way.  However within the UN Strategic Development Goals (SDGs), the proxy of ‘ICT skills’ is of direct relevance to Target 4.4 ‘Skills for work’ and has led UNESCO to propose ‘using basic arithmetic formulas in a spreadsheet’ as a component of an ‘ICT skills acquisition’ element of measuring progress on SDG4.4.

Although there is a shift from superficial surface level ICT skills to deeper cognitive skills and computational thinking, he sees a risk of policy makers jumping straight from ICT skills to traditional computer programming education, thereby missing out the ‘spreadsheeting as entry-level programming’ stepping stone. ICT for Development is now mainstreamed within key UN agencies (ITU, UNCTAD, UNDP, and UNESCO). He concludes that an initiative should be undertaken to disseminate existing spreadsheeting research to education policy makers working with low-income countries.

 

Friday 12: Training Sessions

Working through Modeloff example problems

David Lyford-Smith, ICAEW

David worked through two games from the Modeloff questions resources as an example of problems that require no domain knowledge but are capable of solution by Excel. Indeed, the drive for more efficient solutions often captures the attention of Excel nerds beyond the point of return on time spent.

Dynamic Arrays in Excel

Charles Williams, Decision Models Ltd

Charles worked through a presentation created by Joe McDaid of Microsoft which explained how the new dynamic arrays work and also, illuminatingly, how the legacy array formulas and their implicit intersection rules really worked! This is the video from Microsoft:

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Excel-Advanced-spreadsheet-modeling-using-Dynamic/td-p/726918

Powerpoint slides:

https://medius.studios.ms/video/asset/PPT/MBAS19-BRK4001

Slide 32 (Array Eval) has a typo, the word “didn’t” should not be there. Slide 42 (Calculation Modes) table is blank in edit mode but fills in by animation in presentation mode. The content can be seen in this tweet from Charles’ presentation: https://twitter.com/ExcelAnalytics/status/1149633726592114688

Patrick O’Beirne  16 July 2019

A look ahead to Eusprig 2019

The 20^th annual conference of the European Spreadsheet Risk Special Interest Group (http://www.eusprig.org) will be held on July 11 & 12 at The Barrister’s Court, Browns, 82-84 St Martin’s Ln, Covent Garden, London WC2N 4AG, UK.

David Lyford-Smith of the Institute of Chartered Accountants in England and Wales (ICAEW) will review the history of their three “thought leadership” papers on good practice in spreadsheet use and spreadsheet work environments.  Anyone involved in spreadsheet training, teaching, and syllabus development will be interested to hear how the process of making them has helped ICAEW to develop its position. David also features in the Friday morning tutorials.

Last year Roger Turner of Wesleyan Assurance Society presented a high-quality paper which described their plan to address EUC risks, which the audience found very relevant. This year he describes the challenges which were faced and how these were overcome. I am particularly looking forward to this practical presentation.

During the year, Paul Mireault of SSMI International invited seasoned Excel developers to participate in a challenge to implement a forecast modelling spreadsheet with four variables – product, region, sector, time. He will report on the different implementation strategies employed. Having tried it myself, I can say you will see some radically different approaches to the challenge! Expect arguments from the attendees that “my way is the best because….” !

Simon Thorne and Jamie Hancock  of Cardiff Metropolitan University will present the findings of a case study of spreadsheet use in a higher education institution in the UK. The implications of the results are then considered in terms of accurate reporting to external funding bodies such the funding councils, internal data integrity and internal data efficiencies.

Mária Csernoch of the University of Debrecen, Hungary, will talk about how school pupils can scrape data from web pages rather than type it in for their Excel exercises. Tables and table-like structures on webpages are excellent sources for teaching spreadsheeting by utilizing and developing knowledge-transfer items, presenting various real world problems and solutions, discussing and debugging, and in general, developing computational thinking skills.

Chris R. Roast of Sheffield Hallam University will describe the design development and evaluation of an Excel add-in for interactive visualisation of spreadsheet formulas (EQUS). The development process was one of iterative refinement engaging a initial target audience of mid-teen learners, involving re-design and formative evaluation. The resulting visualisation techniques have been found to be broadly relevant to spreadsheet users beyond the initial target audience.

David Birch and others from Imperial College and  Filigree Technologies, London, expose hidden linkage structure by transforming spreadsheets into fine-grained graphs with operators and values as nodes. They propose that researchers should seek techniques to redraw these boundaries to create higher level structures which will more faithfully represent the end-user’s real world/mental model.

Peter Bartholomew of MDAO Technologies Ltd will discuss the thesis that the adoption of the new Dynamic Arrays in Excel within a more professional development environment could replace traditional techniques where solution integrity is important. This will be a useful taster to Charles Williams’ tutorial on Friday morning.

Marc BRAUN of Xcubes argues that a cube-based system offers a structured and scalable approach to modelling, forecasting, reporting and data analysis with little manual intervention once the model is set up. A practical demonstration will be made with XCubes, a Multidimensional Spreadsheet System software package.

Morten Siersted, visiting research fellow at Bath, presents his dissertation on “Does the teaching of spreadsheeting skills have the potential to be a significant global education policy intervention?” Anyone involved in promoting EUC skills will be interested in hearing his views on the relevant ‘ICT for development’ frameworks.

The innovation this year is a pair of tutorials on Friday morning. David Lyford-Smith will be illustrating good spreadsheet structuring by working through Modeloff example problems. Charles Williams of Decision Models Ltd, the Excel MVP, will be explaining Dynamic Arrays in Excel, their advantages and their wrinkles.

I’ll be there!

Register at http://www.eusprig.org

 

 

UK HMRC MTD OAuth2 Excel/VBA demo

I’m making available as open source my VBA routines for the “Hello World” tests on the UK HMRC “Making Tax Digital” (MTD) web service which use oAuth2 and JSON. Thanks to posts from Bruce McPherson (Excel Ramblings), Tim Hall (Excel-REST), and Kyle Beachill.

Link to Excel workbook

HMRC publishes more information on Making Tax Digital
https://developer.service.hmrc.gov.uk/api-documentation
Navigation menu at the top offers: Documentation, Applications, Support
Checklist of things to do on HMRC website in order to complete these exercises
1. Read Getting Started
2. Register for an account and sign in
3. Read the Testing instructions
4. Add an application
5. Subscribe to the APIs you want. Links to test subs in VBA
Hello World API Open Endpoint
Create Test User App Endpoint
6. Get Sandbox credentials from the Application page
and paste into the fields on the Parameters sheet Application Parameters
The parameters ending in _uri are those currently defined by the HMRC API.
7. To test user-restricted endpoints, you need to create one or more test users.
The Create Test User Service (see picture on the sheet) does it manually.
Copy the generated data into the Parameters sheet at “Generated Individual”

If you’d like to do real work with this, such as submitting VAT or PAYE returns, contact me to discuss a specification and contract.

 

MS Azure Cognitive Services V3 API Translator VBA

I have now updated my free open-source VBA code for the MS Translator API V3, which uses JSON data formats rather than the previous XML. There is no change in your subscription method, you can continue to use the same Azure Account Key.
To use this code:

1. Download the ZIP file (13K) and unzip it into a folder.
Updated 9-May-19 ClsJSONLib.cls, 22-Feb-2021 remove reference to Excel.ScreenUpdating.
Updated 30-Mar-21 ClsJSONLib.cls to handle wider range of Unicode characters.

2. Read the README.TXT file.

3. Run the Sub testMicrosoftTranslate() to verify that the code works

Comments and questions welcome. If you would like some tailoring to your specific requirements, or indeed any Excel VBA development, contact me to discuss a specification and quotation.

Extend Excel: London 18 Oct

Charles Williams is organising  a conference in London on October 18th, 2018. I’ll be there! This is a rare chance to meet leading developers from all over the world and hear directly from MS on Excel Extensibility. Expect to be seriously stretched. It’s a community event, not for profit, so the price for corporate-funded attendees and vendors is only £120 including VAT.

More information & booking: https://developexcel.wordpress.com

• Charles Williams (UK) – Excel MVP:  Overview of extensions, APIs and Tools
• Juan Balmori (USA) – Senior Program Manager, Microsoft Excel Extensibility:  Javascript APIs, UDFs and UI elements
• Govert van Drimmelen (SA): Excel-DNA: .Net for Excel
• Tony Roberts (UK): PyXLL: Python for Excel
• Bruce Belson (AUS): Planatech XLL Plus: C++ for Excel
• Gašper Kamenšek (SL): Power Query + M in VBA
• Simon Murphy (CH): VSTO, Addin Express and other Dev Tools: real world experience

Network with, socialise and meet other Excel developers of addins and solutions

 

Excel and PowerPoint 2016 for Mac gripes

I’ve been trying to maintain compatibility between Mac and Windows versions of Excel/VBA apps for a while now, and it’s a pain.
These notes refer to versions 16.14 of both Excel and Powerpoint.

Here are some of my recent threads on answers.microsoft.com

Passing a user-defined object to a sub As Object passes an object with all Null properties. Passing As MyClass works. Methods work, just not properties.
Interoperability between Excel 2016 for Mac and Powerpoint 2016 for Mac is very frustrating.
To get VBA to copy a picture of a chart to a slide, I’ve tried four different ways.
Only in the latest Insider Build 16.14 does the obvious Copy / Paste method work but even that requires copying and pasting as an image in Excel first and then copying amd pasting the static picture.

1) Manual copy and paste works, as it does in Windows. I want to automate this for what could be 100 slides.

2) The CopyPicture method fails for all objects. Range gives error 1004 CopyPicture method of Range class failed. ChartArea gives error 438 Object doesn’t support this property or method. Chart.Copypicture the result when pasting is a shape with text “The picture can’t be displayed”. (Update 8-May-18: Excel 16.14 (180507) Chart.Copypicture now works, includes a TIFF format in the copied data. And to save a picture of a range as a file, we can now do range.copypicture followed by adding a temporary chart by chartobjects.add and then chartobject.chart.paste of the picture, then chart.export as PNG)

See my post on answers.microsoft.com.
To work around this, create the picture in Excel:

 oChart.ChartArea.Copy
 ' ws is the target sheet for the picture
 ws.Cells(30, 9).Select 'below the chart; to anchor the Paste
 ws.PasteSpecial 'default Format Link:=False,DisplayAsIcon:=False
 'Format: 0=PNG,1=JPEG,2=GIF,3=Enhanced metafile,4=Bitmap,5=DrawingObject
 ' but UI offers only PDF/TIFF/Drawingobject
 Set oShp = ws.Shapes(ws.Shapes.Count)
 oShp.Copy

 

3) There is no Slide.PasteSpecial method in Mac Powerpoint VBA. Slide.Paste takes NO data type argument.

4) ApplescriptTask can be used to execute the Paste in Powerpoint. Thanks to Ron de Bruin for help here.

This code is in the VBA:

    myChartObject.Copy
    AppleScriptTask "CopyPasteScript.applescript", "PastePowerpoint", ""

Save this code in a file eg “CopyPasteScript.applescript” in the special folder
/Users/myusername/Library/Application Scripts/com.microsoft.Excel

    on PastePowerpoint()
    -- Paste into Powerpoint
    tell application "Microsoft PowerPoint" to activate
    delay 0.1
    tell application "System Events"
    tell process "microsoft powerpoint" to keystroke "v" using command down
    end tell
    end PastePowerpoint

5) Another method is to export the Chart as a picture and then import it in PowerPoint

    Dim oChart As Chart, sFile As String
    Set oChart = ThisWorkbook.Worksheets(1).ChartObjects(1).Chart
    oChart.Parent.Activate ' Chartobject
    sFile = ThisWorkbook.Path & Application.PathSeparator & "test.png"
    oChart.Export sFile, "png" 
    ' Only works on Excel for Mac 16.14 or later; 16.9 to 16.13 produces zero bytes file
    oSlide.Shapes.AddPicture(sFile, False, True, 185, 110, -1, -1)

 

6) In practice, the above method is agonisingly slow, so what I really do is write a VBA script with lines that create a presentation, add slides, set the title text, and call AddPicture using the file names created.
Then I create a temporary presentation in PowerPoint, import that VBA code, execute it, and close the temporary presentation without saving it.

7) Before Chart.Export started to work in Excel 16.14 or later, I did try the ugliest workaround, and it works but it’s really a kludge.

7.1 Create a temporary workbook with a sheet for each slide and copy/paste the title and chart image into it. Add needed properties to the .AlternativeText of the pictures.

7.2 Save the workbook as html.

7.3 Open the tabstrip.htm file, read the text, split it at “<a href=” and extract the tab names.

7.4 for each tab file name, open its htm file, read the text, split it at “<v:shape id=” and parse out the picture file names and properties.

7.5 Now I have the picture file name, add a line to the PowerPoint VBA file I create in (6) above to add a slide using the right title and picture filename.
Horrible, isn’t it?

 

Dublin Tech Summit 18-19 Apr 2018

#DTS18 @dubtechsummit

Dublin Tech Summit is a two day (Wed 18 and Thu 19 April 2018) meetup of startups and business people intended to connect investors to tech companies, and to highlight emerging tech trends.
I attended as a guest of Failte Ireland, and brought two UK attendees to it, to help promote Ireland as a conference destination.

Several side tracks dealt with start-ups, mentoring, and Eir offered a prize of €5,000 for the best startup pitch. This looks like a good event for any tech company at launch or early development. If you can’t afford it, maybe (in Ireland) talk to your LEO (Local Enterprise Office) for support, or the equivalent in other countries. For example,  La French Tech, an initiative of Business France, featuring Kuzzle, Axione, Qare, and Keymetrics. I saw a number of other stands from French companies; one that caught my eye was Myoeno.com, a wine scanner device to measure oenological characteristics.

My opinions (and bear in mind I’m probably not its target market):
It is not a training event, there were no real techie or “how to” presentations, but an educational / product promotional event in a general sense loaded with “fantastic / incredible / amazing” buzzwords and hype. I understand that slides will be available later.
The schedule only gave speaker names and very few descriptions of what the talks were about. This did not improve as the event grew closer, so it became clear that speakers were chosen for being able to give engaging and fluent presentations, and not for promising any useful content. The general sense is of watching an animated magazine article. The panel sessions featured experts, including local ones such as Brian Honan, but the limited time meant that topics were necessarily high-level discussions rather than informative.

Quantum Computing, Bo Ewald, D-Wave systems: because many terms were used without definitions or examples, like “annealing”, this would have made more sense to me if I had known more about the topic, but the 2017 story of China communicating with a satellite using “entangled” photons was interesting.

Patricia Scanlon of Soapbox Labs described the particular challenges of recognising children’s voices.

The Publishing Revolution session spoke of developments in eBooks and interactive learning and self-publishing.

Fred Laluyaux of Aera spoke of the “self-driving” enterprise. Their solution to integration and interoperability of 140 separate IT systems is to introduce a 141st and link every other one to it to create a “digital twin” to capture corporate memory. Maintenance, anyone?

The MusicXTech events were in large nearly empty rooms, where other tracks were packed out.

One MusicXTech session interested me, on “The Visualisation of Music” but the schedule had no information on the speaker and no abstract. I tweeted the organisers asking for more info but got no reply. I went anyway but it was nothing to do with the visualisation either of musical or music notation, but rather a look back of how the early augmented reality scenes in the Gorillaz ‘Humanz’ app were created by B-Reel and how it’s much easier now. A bit of pop history, maybe.

The Casey Neistat session was entitled “How to be heard” but he gave no “how to” information at all – in fact, disclaimed it. It was simply the story of his YouTuber career and probably interested people in media.

Jason Zander of Microsoft described the Azure eco system – he began by saying that at a dev conference the first thing he’d do is launch into Visual Studio so that sounds more like my kind of event.

At the panel on Bitcoin, an audience show of hands showed that more than 50% claimed to own Bitcoin. They expect the performance problems to be addressed someday – currently BTC handles 7 transactions per second but Visa handle 24,000 tps.

A session entitled “The Future is Dominated by Customer Experience​” sounded promising but again came across as a rehash of fairly obvious “customer first” assertions. It might have been of more interest to discuss why this does not actually happen in practice, what the motivations are that result in CX failure. Like the app for this event.

Jordan Bitterman of IBM described current applications of AI. He gave examples like Shazam for song recognition, “Do not pay” to challenge parking tickets, and voice recognition identifying which adolescents showed mental health risks. He also reported on using location data to infer possible forced migration of refugees before they are washed up on shore.

The Grip App for the event was my Gripe App because it was useless for planning my attendance. It had networking features but nobody I clicked as “interested” responded, perhaps because of the thousands of attendees, the few they chose to show me were not particularly relevant. They trumpet the “AI” aspect without saying what that means – probably just dumb text matching between stated interests. There is no search option in the Grip app. Well, there is but when I typed in the name of a speaker I was interested in hearing I got one result dated 1st April 2016 and no further info.
The only way to view the schedule is to select one track, then one day, then scroll down, tap the down arrow beside each session title in turn to see who the speakers are and any description, read it to see if it’s what you want, and if not then scroll down more and repeat. And repeat all that for the next day. And repeat all that for each of the four tracks. What a crock.
If anyone designing the app had much experience of attending a conference they would know the top task is to select which sessions to attend in which tracks.
I need an overview of the whole schedule with detail of all the talks. If I tap on a speaker, I want to see what sessions they are in and when and where.
There is a grid overview offered but most of the time it does not download. (I subsequently learned that was due to demand throttling.) And when it did, it was view-only in the reader, it could not be saved on my device so I would not have to download again. The smart solution is to print the schedule yourself before coming.
Or as some do, take a picture of the session displays on the screens outside the rooms. And do it while it’s on show – part of the time it wipes away and come back, for no discernable reason except to frustrate the reader.
A simple backup system for the organisers would have been to give everyone an A4 sheet with the parallel sesssions listed, to enable planning.
On the second day I took the time to scroll the schedule early morning and write down my choices on paper.

 

Free Add-In to profile VBA speed and coverage

This post is now replaced by an update: https://sysmod.wordpress.com/2021/07/06/vba-performance-profiler-timing-procedure-execution/

———————————————-

I’m making available a beta version of my VBA performance profiler for people to try it out and let me know what they think. I’m interested in seeing what kinds of VBA code trip it up and produce syntax errors.

It adds profiling code to your VBA project which collects procedure timing and code coverage statistics. Here is what the Help form shows:

INSTRUCTIONS

Open the file XLTEST_VBA_PROFILER.xlam and enable macros, or install it as an add-in.

Open the workbook you want to profile. Save it under a different name to avoid overwriting the original.

On the Developer tab, in the VBA Profiler group, click the button “Profiling”.

This gives a form in which you can specify whether you wish to:

1. Add a call to every line of code to trace its execution for coverage analysis.
A tab-delimited text file is output of all the code after trace lines have been added
so that the Sub VBA_Profile_Reports can show what lines of code were executed.
The text file is named from your workbook name suffixed with _VBA_COVERAGE.TXT

2. Collect Profile timing data (needed to produce profile reports)
If unchecked, it sends the output to Outputdebugstring.
You can watch that output using TraceSpy (https://tracespy.codeplex.com/)
To be sure that TraceSpy can keep up with the output, do not use Auto-Scroll.

3. Export all the VBA to a text file before making the changes.
This is just for your record, it is not used in this process.
The tab-delimited text file is named from your workbook name suffixed with _VBA_BEFORE.TXT

When you click OK, it adds a line to every procedure in the project, unless its declaration line contains a comment ‘@notrace

After it is finished, go into the VBE and click Compile to check that no syntax errors have been introduced. If you get a Compile error: Variable not defined VBA_Profiler then you need to add the Tools Reference to VBA_Profiler although that should have been automatically added by the profiler.

If you checked Profile timing above, this line looks like this:
Dim P_ As Object: Set P_ = VBA_Profiler.New_Profiler(): P_.Start 12345, “modulename”, “procname”
Otherwise it looks like
Dim P_ As Object: Set P_ = VBA_Profiler.New_Debugger(): P_.Start 12345, “modulename”, “procname”

The way that works is that when the procedure exits and the variable P_ goes out of scope, the profiler counts up the number of calls and the length of time the procedure took. The resolution of the timer is about a microsecond.

If you checked code coverage above, it inserts a call at the beginning of every exceutable line like this:
P_.O 12345&:

In the examples above 12345 is a unique line number in the project, corresponding to lines in the coverage file.

Finally, insert a test sub which initialises the profiler, calls your main line procedure, and then produces reports.

Sub testMyProc
 VBA_Profile_Initialize ThisWorkbook
 MyProc
 VBA_Profile_Reports 
End Sub

VBA_Profile_Reports outputs a tab-delimited profile report named from the workbook name suffixed by _VBA_PROFILE.TXT;
and if you do the coverage analysis, a workbook is created with one sheet containing the contents of the _VBA_COVERAGE.TXT file with an extra column added to show the number of times a line of code was executed.
The workbook is unsaved, you can save it as a .xlsx file if you wish to keep it.

Let me know what you think!

Tracing & Profiling VBA speed with TraceSpy

I’m interested in measuring VBA performance. I add tracing calls to the routines so I get the time on entrance and exit. I also optionally add a call to trace every line executed, which I use when I get unexpected crashes (“Excel has stopped working”), so that I can see the line it crashed on. Of course, Debug.Print is no use in that so I use the Windows API OutputDebugString. I used to monitor that by DebugView but was disappointed to see when I examined the log that dbgview.exe missed some output. It looks like if one call – eg an entry into a fast sub – is followed too quickly by another, DebugView only reports the second output. That makes it impossible for me to calculate the elapsed time for each sub. So I looked around and found TraceSpy. It kept up very well. The only thing I had to do was to turn off its Auto Scroll. With auto-scroll it looked like its queue filled up and it would take a half hour to finally work through all the debug messages, and if VBA was too fast, it would miss the last half of them. So I also put a Sleep 0 after OutputDebugString to get Excel to give other processes a chance. It doesn’t need DoEvents, I’m not looking to process events in Excel itself, just to give TraceSpy some time. TraceSpy does not log to file but it is easy enough to click on the first log line, Shift+End to select to the end, Copy entire line, and paste into Notepad++ to be able to save it. My longest log files so far are 600,000 lines, so I have not yet hit the Notepad++ file size limit.

When I saw the results, I could see which functions were slow and needed optimising so that the overall execution speed would be improved.