Speed of populating a Ribbon Bar

[Roderick]

I'm using Word 2016 now and Windows 10.

I have created a template for use in two different languages and written the XML and have used 'getLabel', ''getScreenTip' and 'getSuperTip' where appropriate in all the controls.

All the labels are in an Excel file with all the control IDs in three separate named ranges to cater for the getLabel, getScreentip and getSupeTip.

I have the following 'getLabel' procedure to label each of the controls in the XML. Everything works perfectly: the labels get called correctly and the Ribbon Bar gets loaded properly.


>>>>>>>>>>>>>>>
Sub RDB_getLabel(control As IRibbonControl, ByRef returnVal)
Dim wsht As Excel.Workbook
Dim wksheet As Excel.Worksheet
Dim inifileloc2 As String
'Reads the location of the Office Details spreadsheet
VarPathLocal = Options.DefaultFilePath(wdUserTemplatesPath)
VarPathNetwork = Options.DefaultFilePath(wdWorkgroupTemplatesPath)

FullPath = VarPathLocal + "\" + "Office Details.xls"
FullPathNet = VarPathNetwork + "\" + "Office Details.xls"

If Dir(FullPath) <> "" Then
inifileloc2 = FullPath

ElseIf Dir(FullPathNet) <> "" Then
inifileloc2 = FullPathNet
Else
End If

On Error Resume Next

Set wsht = GetObject(inifileloc2)
Set wksheet = wsht.Worksheets("Letterhead")

returnVal = wsht.Application.VLookup(control.id, wksheet.Range("rdbLabels"), 3, 0)

If Err.Number > 0 Then
returnVal = "Error"
Err.Clear
End If
On Error GoTo 0

End Sub
>>>>>>>>>>>>>>>The problem I'm getting is that the procedure to label all of the controls is taking 50 seconds before the Ribbon Bar appears! Yes, there are, in fact, 46 getLabel calls so one could say "What do you expect?".

Could there be another way of achieving the same result, but getting the Ribbon to load that much faster, please?

Some guidance would be much appreciated.

[Bob Phillips]

I wouldn't expect 50 seconds for 46 controls, that is for sure.

I would suspect that the time is going on all of that initialisation stuff, down to GetObject.

I would suggest to set wsht and wksheet as public variables,having that code in a separate procedure, and have another Boolean variable that is tested to see whether the initialisation is done.

Something like, but not necessarily all of, this ...


Option Explicit

Public wsht As Excel.Workbook
Public wksheet As Excel.Worksheet
Public ExcelInitialised As Boolean

Sub RDB_getLabel(control As IRibbonControl, ByRef returnVal)

If Not ExcelInitialised Then Call ExcelConnect

returnVal = wsht.Application.VLookup(control.ID, wksheet.Range("rdbLabels"), 3, 0)

If Err.Number > 0 Then
returnVal = "Error"
Err.Clear
End If
On Error GoTo 0

End Sub

Sub RDB_getScreentip(control As IRibbonControl, ByRef returnVal)

If Not ExcelInitialised Then Call ExcelConnect

'etc.
End Sub

Public Function ExcelConnect()

Dim inifileloc2 As String
'Reads the location of the Office Details spreadsheet
VarPathLocal = Options.DefaultFilePath(wdUserTemplatesPath)
VarPathNetwork = Options.DefaultFilePath(wdWorkgroupTemplatesPath)

FullPath = VarPathLocal + "\" + "Office Details.xls"
FullPathNet = VarPathNetwork + "\" + "Office Details.xls"

If Dir(FullPath) <> "" Then
inifileloc2 = FullPath

ElseIf Dir(FullPathNet) <> "" Then
inifileloc2 = FullPathNet
Else
End If

On Error Resume Next

Set wsht = GetObject(inifileloc2)
Set wksheet = wsht.Worksheets("Letterhead")

ExcelInitialised = True
End Function


The other option would be to read them all into an array on that first connect and access the array, avoid the inter-op aside from the initial time.

[Paul_Hossler]

Other ways -- not tried --


1. Put the Excel reading logic into onLoad, and save the 3 ranges in arrays. You could go with an Excel-generated CSV file and just read it so you don't need to CreateObject an Excel instance.

getLabel should just get the appropriate label from the array


2. The fastest way would be to just hard code the 3 sets of both languages stored within the VBA. Again I think onLoad could select which array to use


3.Another (more complicated) approach would be to use CustomXML and store it in the DOTM file. Several of the Word gurus here have web sites that can help


Personally, I'd use #2 since it keeps it self contained and I don't need to drag around another file

[Roderick]

Thanks for all the ideas. I sure learned a lot from them!

One way I got to a solution was following Paul's suggestion above using No. 1 as a basis for the solution. However, I thought maybe using Access could solve the problem: and it did!

I can't claim any credit for my solution - I just followed other contributors' ideas and came to a result.

Thanks to everybody!

[SamT]

I vote for Paul's #2

[Tereasa]

I would have suspected something but there is given comprehensive information! For that I am very grateful !

[RandyBonnett]

Hello guys,

Thanks for the suggestions and ideas. I'm not a professional yet, so it was quite difficult for me just to find the needed information. I also found a good article by Preston Gralla on Word 2016 and 2019 Ribbon interface features (how to hide and show a Ribbon toolbar easily).
Edit: I'll add the resource later.