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

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.

Rubberduck VBA addin

The Rubberduck VBA (@rubberduckvba) open source project has been going for a couple of years, and when I was reminded of it recently I decided to give it a try. It’s definitely a help in VBA development and I’ll go on using it with Excel.

It’s an addin for the VBE add-ins manager. I am currently using version 2.0.11.2453, downloaded from Github. I look forward to some enhancements they discuss on Github.

I focused on Code Inspections, where Rubberduck warns you about potential quality issues by inspecting the code in a VBProject, either Excel or Access. It also has Refactorings and Unit Tests, which I shall look at later.

The list of inspections is here. Some of them I don’t enable (eg “Encapsulate Public Field”, “Multiple Declarations”) as they are not issues for me, although the style police might think otherwise. Others I ignore with a comment flag in one specific place but leave the inspection active in case the poor style arises by mistake elsewhere.

I found one problem in one of my projects using this, so it’s already worthwhile.

The name, in case you’re curious, derives from the saying that if you don’t have a peer to review your code with, explaining it to a rubber duck helps you see things you missed first time.

And finally, if you have a special interest in Excel VBA code quality, please consider sharing your knowledge with others by presenting at the annual conference of the European Spreadsheet Risk Interest Group (@eusprig) . July 6, Imperial College London. http://www.eusprig.org/call-for-papers.htm

VBA code for Microsoft Text Translator API

I have now updated the translator VBA source code to V3.

https://docs.microsoft.com/en-us/azure/cognitive-services/translator/migrate-to-v3

V2 was deprecated on April 30, 2018 . The Microsoft Translator Hub was to be retired on April 30, 2019. View important migration information and dates.

On Apr 30 Microsoft extended availability of version 2 of the Translator Text API:
“After receiving strong customer feedback about the scheduled retirement of version 2, we’ve decided to continue availability of this version along with version 3. Your applications that use version 2 will remain functional. Although version 2 will continue to be available, please migrate your applications to version 3 as your development cycle allows. By moving to version 3, you get access to exclusive functions, such as neural models, which are trained through Custom Translator.”

The V2 content below is now obsolete. See the V3 link above for new content.

Microsoft retired the Datamarket Translation API on April 30, 2017.
You need to sign up to the Azure Portal and use the Text Translator API in Cognitive Services.

https://translatorbusiness.uservoice.com/knowledgebase/articles/1078534-microsoft-translator-on-azure
Action Required before April 30, 2017: Microsoft Translator Moves to Azure
How to sign up for Microsoft Translator on Azure

https://www.microsoft.com/cognitive-services/en-us/translator-api
The Free plan allows 2M characters per month.

Documentation and interactive trials :
http://docs.microsofttranslator.com/text-translate.html

You define an Account Name and get two keys. Either work, I don’t know yet what the difference is.

Before using “Try out a Method” on that page, you first use your Key 1 to get an access token that lasts 10 minutes and then you can try the methods.
In the methods, leave the Authorization blank, and for appid specify Bearer followed by a space and the access token.

To use your key in VBA, I now provide a VBA module that I use in Excel for translation.
You may use this free with attribution. I am available for custom Excel/VBA development work.

Download here: http://www.sysmod.com/MSTranslator4.bas

A test with 9,900 phrases in 23 European languages totalling 241K took under 30 minutes to complete.
I did not use Sleep to throttle the performance, and no translations were missed.
Microsoft do not indicate a rate limit, only the Azure Subscription Plan total: 2M characters/month for the free plan.
To learn more about the technology, see
https://hubtest.microsofttranslator-int.com/Help/Download/Microsoft%20Translator%20Hub%20User%20Guide.pdf
“This guide will benefit any person who is interested in building a custom translation system using the Hub. A deeper background in machine translation is not essential to use the Hub. “

My old post from 2012 on the Datamarket translator is at
https://sysmod.wordpress.com/2012/01/09/microsoft-translator-vba-code-sample/

Example:

en Translate
pt Traduzir
fr Traduire
de Übersetzen
fi Kääntää
nl Vertalen
es Traducir
it Traduci
no Oversette
ja 翻訳
zh-CHS 翻译
ar ترجمة

Conditional compilation VBA Excel Mac/Win/32/64…

To make my addin handle all the combinations of Excel 2007 onwards on Mac and Windows, 32-bit and 64-bit, I ended up with this conditional compilation block. The example I use is GetTickCount() but the principle applies to other declarations.

Microsoft say “We expressly DO NOT support external use of Win32 APIs on the Mac, even if we have an implementation of them in the Office frameworks.” If you ever find documentation on these, let me know.

'https://msdn.microsoft.com/en-us/library/office/ee691831(v=office.14).aspx
'Compatibility Between the 32-bit and 64-bit Versions of Office 2010
'Apple has deprecated HFS on the Mac, so Mac Office 2016 no longer uses or supports HFS paths.
'You must use POSIX paths (with "/" as the directory separator) in Mac Office 2016.
' In Macscript, when getting folders or files in Mac Office 2016,
' insert "return posix path of " before "(choose folder..."

#If Mac Then
 #If MAC_OFFICE_VERSION >= 15 Then
  #If VBA7 Then ' 64-bit Excel 2016 for Mac
   Declare PtrSafe Function GetTickCount Lib _
"/Applications/Microsoft Excel.app/Contents/Frameworks/MicrosoftOffice.framework/MicrosoftOffice" () As Long
  #Else ' 32-bit Excel 2016 for Mac
   Declare Function GetTickCount Lib _
"/Applications/Microsoft Excel.app/Contents/Frameworks/MicrosoftOffice.framework/MicrosoftOffice" () As Long
  #End If
 #Else
  #If VBA7 Then ' does not exist, but why take a chance
   Declare PtrSafe Function GetTickCount Lib _
"Applications:Microsoft Office 2011:Office:MicrosoftOffice.framework:MicrosoftOffice" () As Long
  #Else ' 32-bit Excel 2011 for Mac
   Declare Function GetTickCount Lib _
"Applications:Microsoft Office 2011:Office:MicrosoftOffice.framework:MicrosoftOffice" () As Long
  #End If
 #End If
#Else
 #If VBA7 Then ' Excel 2010 or later for Windows
  Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
 #Else ' pre Excel 2010 for Windows
  Declare Function GetTickCount Lib "kernel32" () As Long
 #End If
#End If

 

See http://www.rondebruin.nl/mac/ for lots more on Excel for Mac, in particular Macscript code for file and folder dialogs.

For Windows API declarations with the new LongPtr type, see

http://www.jkp-ads.com/articles/apideclarations.asp

My previous posts on Excel for Mac are here:

https://sysmod.wordpress.com/2011/10/21/list-of-mac-excel-2011-differences-from-excel-2011/

https://sysmod.wordpress.com/2011/10/24/more-differences-mainly-vba/

https://sysmod.wordpress.com/2011/10/28/mac-excel-vs-pc-excel/

 

UPDATE 14-March-2019

Excel VBA is now throwing an error 53 file not found for

“/Applications/Microsoft Excel.app/Contents/Frameworks/MicrosoftOffice.framework/MicrosoftOffice”

So the GetTickCount() function is no longer available, unless any reader has an idea.