Consulting

Results 1 to 7 of 7

Thread: Speed of populating a Ribbon Bar

  1. #1

    Speed of populating a Ribbon Bar

    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.
    Last edited by xld; 09-11-2017 at 08:49 AM. Reason: Added code tags

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,091
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,755
    Location
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    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!

  5. #5
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,964
    Location
    I vote for Paul's #2
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  6. #6
    VBAX Newbie Tereasa's Avatar
    Joined
    Jul 2019
    Location
    United States Texas
    Posts
    1
    Location
    I would have suspected something but there is given comprehensive information! For that I am very grateful !

  7. #7
    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.
    Last edited by RandyBonnett; 09-15-2019 at 10:53 PM. Reason: edit

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •