none
Excel 2016 VBA performing poorly, especially bad with Application.FileDialog RRS feed

  • General discussion

  • I have a lot of Excel VBA code that I run on Windows 10 PCs and laptops with Office 2007 and Office 2010 installed. VBA code runs fine in those environments.

    But I have one Windows 10 Asus laptop I purchased in 2016 where I run the same Excel VBA code with Office 2016 . I run into intermittent issues, just general slowness and occasional crashing  and lately very serious issues. For example code involving Application.FileDialog(msoFileDialogFolderPicker) will often not function at all. Other than Excel VBA everything else seems to function fine on this laptop.  The excel VBA is supposed to be running commands on files in a network location . I don't seem to have any trouble accessing these files from File Explorer outside of the VBA. But I just mention this in case it is relevant that the files are not on the hard drive of the computers running the VBA but on a network location.

    I have re-installed Office 2016 twice and that was no help. I updated my operating system and Excel 2016  and that did not help.

    Various degrees of this issue have persisted for almost a year on this laptop. I have given up. I have uninstalled Office 2016 on that laptop and installed Office 2010. So far no problems anymore.

    Are others also having general problems with running Excel VBA code in Excel 2016?


    Batkis



    • Edited by batkis Sunday, June 4, 2017 2:32 AM
    Saturday, June 3, 2017 8:51 PM

All replies

  • Hi Batkis,

    1. Which architecture of the problematic Excel 2016 in the Asus laptop, 32-bit or 64-bit?
       As you know 64-bit Excel has many restrictions in comparison with 32-bit version.


    2. What is the version and build of it?
        The latest is 1704 build 8067.2157.

        If not the latest, please update it to the latest.

    3. Have you tried to Repair Excel?
        If not, try to repair.
    Repair an Office application
    https://support.office.com/en-us/article/Repair-an-Office-application-7821d4b6-7c1d-4205-aa0e-a6b40c5bb88b

    4. Are there some third-party add-ins/extensions in it?
       If yes, try to remove it.

    ___________
    Ashidacchi       

    • Edited by Ashidacchi Sunday, June 4, 2017 4:22 AM
    Sunday, June 4, 2017 4:21 AM
  • Thanks  for your response Ashidacchi.  I guess I should have waited to uninstall it until after posting to be absolutely sure of my responses here (particularly with respect to #1), but to the best of my knowledge none of the factors you mention would have impacted my situation.

    1. I would have installed the default version 32 bit. I can't imagine why I would have changed   to 64 bit from the default 32 bit  installation.  By the way, precisely what restrictions would affect the 64 bit performance?

    2. I deliberately went into excel and used the update options from within excel 2016 so I know I had the latest build.

    3. Yes I did try using the "repair" option

    4.I had removed all 3rd party add in. At one point I think I tried to install an add in called "Name Manager" which I liked using  in Excel 2010 but I removed it. I don't think I was able to get it to work in 2016.

    I am not 100% sure about #1 so maybe in the future I may try to install this 2016 on another PC or laptop in my network. I was just wondering if any other people are experiencing excel 2016 VBA issues in general? Or was it just me and this particular laptop/network configuration? I have gone through so many iterations trying to get Excel 2016 to work on this particular laptop, as I say, I have given up on that one.


    Batkis



    • Edited by batkis Sunday, June 4, 2017 3:17 PM
    Sunday, June 4, 2017 3:16 PM
  • 1. I would have installed the default version 32 bit. I can't imagine why I would have changed   to 64 bit from the default 32 bit  installation.  By the way, precisely what restrictions would affect the 64 bit performance?


    Batkis


    Hi Batkis,

    About #1, I don't mean performance, but features between 64-bit and 32-bit Excel. 
    Choose between the 64-bit or 32-bit version of Office
    https://support.office.com/en-us/article/Choose-between-the-64-bit-or-32-bit-version-of-Office-2dee7807-8f95-4d0c-b5fe-6c6f49b8d261

    Sorry, I cannot understand the meaning of "the excel VBA is supposed to be running commands on files in a network location", especially "running commands on files". Could you explain it using other words?
    __________
    Ashidacchi
    • Edited by Ashidacchi Monday, June 5, 2017 3:48 AM
    Monday, June 5, 2017 3:48 AM
  • Thanks again.

    I can't see mention of any features relevant to my use of Excel and Excel vba in the webpage you share above.

    I will explain in other words the meanings which were unclear.  I was trying to explain that the workbooks containing the excel macros and excel data do not reside on the computers that are running the macros and accessing the data. Each computer has its own copy of Excel and is running Excel macros and accessing Excel data that resides on one particular computer in our network. Only one computer was having trouble running these macros and that was the one with Office 2016 installed.  After installing Office 2010 on that problem computer, I am not longer having problems at this point.


    Batkis

    Monday, June 5, 2017 2:57 PM
  • Hi Batkis,

    What problems did you experience with Name Manager?


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

    Tuesday, June 6, 2017 4:23 AM
  • I can't see mention of any features relevant to my use of Excel and Excel vba in the webpage you share above.

    1. Please read the section "Reasons to choose the 32-bit version".
      It refers to the difference between Excel 32-bit and 64-be. 
      Choose between the 64-bit or 32-bit version of Office

    If you copy all the related Excel files to your local drive and execute macro, what will happen?
    _____________
    Ashidacchi
    Tuesday, June 6, 2017 4:44 AM
  • Does your code do any (un)protecting of worksheets or the workbook? If so, please be aware that changing protection has slowed down by a factor of as much as 2000 since the upgrade to Office 2013, due to a change in the password hash algorithm.

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

    Tuesday, June 6, 2017 5:53 AM
  • I should clarify that "Name Manager" was a third party add-in that I installed in Excel 2010 but it did not seem to be capable of installing in Excel 2016. This is not to be confused with the "Name Manager" this is on the Formulas tab and part of the regular Excel functionality. The Name Manager on the Formulas tab worked as expected.

    The Name Manager add-in was useful mainly because it enabled you to easily change the scope of a range name between workbook and worksheet. But as I said it did not appear to be compatible with Excel 2016.


    Batkis

    Tuesday, June 6, 2017 4:46 PM
  • As I said I am pretty sure I had the 32 bit version but in any event I can't see anything relevant to my use of Excel and Excel VBA in the link you provide.

    As for using the local drive with Excel 2016, I do recall experiencing better performance in the local drive but still not as good as Excel 2010.


    Batkis

    Tuesday, June 6, 2017 4:48 PM
  • Thanks Jan. This would explain something significant. I do protect all my worksheets and so this would explain why there were strange pauses in running every procedure . Almost every VBA application I write involves first running a procedure to unprotect the worksheet whose data is being altered and then afterwards running a procedure to protect the worksheet.

    Still this protection issue cannot be the only cause of my issues, because as I indicated to Ashidacchi, I believe there was a significant change in performance between using a network location vs the hard drive. Not merely a pause, but frequent crashing.

    So I continue to wonder if there is some Excel 2016 issue related to accessing data on a network location? But I have not heard anything from anyone to corroborate that theory. Excel 2010 is not having this issue.


    Batkis

    Tuesday, June 6, 2017 4:57 PM
  • I'd like to confirm again. Have you already removed the add-in "Name Manager" from Excel 2016?
    Wednesday, June 7, 2017 12:00 AM
  • When I checked whether any add-ins were installed, it was not installed. If I recall correctly I tried to install it, but it would not install so it is a mute point.

    Batkis

    Wednesday, June 7, 2017 1:18 AM
  • Hi batkis,

    Thank you for reply.
    Would you provide your code related to Application.FileDialog or something wrong?
    ___________
    Ashidacchi
    Wednesday, June 7, 2017 1:33 AM
  • I know that, I created Name Manager you are referring to together with my fellow Excel MVP Charles Williams. Hence my question what was wrong as it works just fine on my Excel 2016.

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

    Wednesday, June 7, 2017 5:14 AM
  • I would start with checking your network drive connections (mappings). Do any appear to fail to register when Windows starts? If so those may cause slow response of the file, open dialog.

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

    Wednesday, June 7, 2017 5:16 AM
  • Thanks Ashidacchi. You are being very helpful so I wish I had posted about this issue earlier before I gave up on 2016 on that particular laptop. But on the other hand, I do hope to install this again on a different laptop so here is the code that I think caused the worst problems:

    Function f_sBrowseFor(sFolderspec As String, sPrompt As String, bFolder As Boolean, bOpen As Boolean) As String
    'this function opens up a Windows file dialogue in order to prompt the user to select a folder or file
    ' the name of the subfolder or file selected by the user is returned
    'sFolderspec is the folder from which the subfolder or file is to be selected
    'bOpen = true means a file is opened
     Dim fd As Object, iItem As Integer
    If bOpen Then
        Set fd = Application.FileDialog(msoFileDialogOpen)
    ElseIf bFolder Then
        Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    Else
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
    End If
    fd.InitialFileName = sFolderspec
    fd.Title = Mid(sPrompt, 1, 126)
    fd.AllowMultiSelect = bOpen
    If fd.Show = False Then
        f_sBrowseFor = ""
        Exit Function
    End If
    f_sBrowseFor = fd.SelectedItems(1)
    If bOpen Then
        For iItem = 1 To fd.SelectedItems.Count
            Workbooks.Open (fd.SelectedItems(iItem))
        Next
    End If
    End Function


    Batkis


    • Edited by batkis Wednesday, June 7, 2017 12:23 PM
    Wednesday, June 7, 2017 12:22 PM
  • Wow, Jan. So you are that guy. That is great to know. I do continue to get a lot of benefit from that add-in for Excel 2010.

    As far as what was wrong when I tried to use it with Excel 2016, perhaps  a year ago, the add-in's compatibility with Excel 2016 was not as firm as it is now? It is over a year ago that I tried to install it with Excel 2016. What I recall is that I was unable to install it. I found myself suspecting that, even though it did not appear to install, my attempt to install it had somehow disrupted Excel 2016 in some way. But in any event, I did later reinstall Excel 2016 and never did attempt to install Name Manager again. Perhaps my apparent compatibility issues were resolved over the past year?


    Batkis

    Wednesday, June 7, 2017 12:35 PM
  • In response to Jan's question about my network connections, these issues are not replicated in Excel 2010 nor when I merely use Windows 10 File Explorer. The only exception is a network issue I sometimes encounter  when I rename a folder in a network location using File Explorer. Sometimes windows seem to freeze at that point. But regardless of that, Excel 2010 seems to work smoothly so whatever network issue I am having does not seem to impact the performance of VBA code in Excel 2010 or Excel 2007.

    Batkis

    Wednesday, June 7, 2017 12:39 PM
  • Hi Batkis,
     
    Perhaps so, but even if 2010 functions OK, this does not mean your setup does
    not have an intrinsic problem Excel 2016 hickups over.
     
     
    Wednesday, June 7, 2017 12:47 PM
  • Hi Batkis,
     
    Perhaps you should try to use it on 2016, it works normally for me.
     
     
    Wednesday, June 7, 2017 12:47 PM
  • OK. It sounds like whatever was going on, is unique to whatever setup I had and not a generic issue shared by many users. Some day I will try installing Excel 2016 on another pc. I have already tried re-installing Excel 2016 twice on this laptop and also repaired office on this particular laptop, so I am absolutely done with that.

    Next time I install Excel 2016 it sounds like it is safest to ensure that I install the 32 bit version ( I am not dealing with enormous datasets). Also I will not be afraid to install Name Manager add in on Excel 2016 in future.


    Batkis

    Wednesday, June 7, 2017 1:17 PM
  • Hi Batkis,
     
    OK. NM should work just fine on 64 bit Office however.
     
     
    Wednesday, June 7, 2017 1:32 PM
  • Hi Batkis,

    Thank you for your providing code.
    I've made a sample macro using your function. It could work fine.
    (Excel 2016 32-bit in Windows 10 Pro 64-bit)

    Here's a screenshot after clicking on a button [batkis] and select a file in FileDialog.


    Here's my code in the button [batkis]
    Private Sub btn_batkis_Click()
        Dim bFolder As Boolean, bOpen As Boolean
        Dim f_result As String
        ' ---
        bFolder = False
        bOpen = True
        ' ---
        Range("B10").Value _
            = f_sBrowseFor("R:\00 北窓舎\01 PCサポート\00 MSDN_TechNet", "Select file", bFolder, bOpen)
    End Sub
    ' --- provided by Batkis (modified a little by Ashida)
    Function f_sBrowseFor _
        (sFolderspec As String, sPrompt As String, bFolder As Boolean, bOpen As Boolean) _
        As String
        ' this function opens up a Windows file dialogue in order to prompt the user to select a folder or file
        ' the name of the subfolder or file selected by the user is returned
        ' sFolderspec is the folder from which the subfolder or file is to be selected
        ' bOpen = true means a file is opened
        Dim fd As Object, iItem As Integer
        If (bOpen) Then
            Set fd = Application.FileDialog(msoFileDialogOpen)
        Else
            If (bFolder) Then
                Set fd = Application.FileDialog(msoFileDialogFolderPicker)
            Else
                Set fd = Application.FileDialog(msoFileDialogFilePicker)
            End If
        End If
        ' ---
        fd.InitialFileName = sFolderspec
        fd.Title = Mid(sPrompt, 1, 126)
        fd.AllowMultiSelect = bOpen
        If fd.Show = False Then
            f_sBrowseFor = ""
            Exit Function
        End If
        f_sBrowseFor = fd.SelectedItems(1)
        If bOpen Then
            For iItem = 1 To fd.SelectedItems.Count
                Workbooks.Open (fd.SelectedItems(iItem))
            Next
        End If
    End Function
    What is a difference between us?
    ______________
    Ashidacchi
    Thursday, June 8, 2017 12:10 AM
  • Hi Batkis,
     
    On my system, both Excel 2010 and 2016 take a very similar amount of time to
    open the file dialog.
     
     
    Thursday, June 8, 2017 5:37 AM
  • Thank you Jan and Ashidacchi. I appreciate the input and while it does not resolve the mystery, at least I can see that this Excel 2016 problem is not prevalent among other users.  At some point in future I hope to install Excel 2016, most likely on some other computer than the one currently causing issues.

    As I indicated, I have uninstalled Excel 2016 and installed Excel 2010 instead on the problem computer and this has resolved all issues. At this point a concern remains that the problem was the combined result of some issue with the way I have the network configured (Homegroup) and perhaps Excel 2016 cannot handle the particular way my Homegroup is configured. Or perhaps there is some particular flaw in the way my Homegroup is configured which is only detectable in Excel 2016.  Unfortunately that would affect all computers in my network so I am reluctant to pursue Excel 2016 further at this stage given that my computers with Excel 2007 and Excel 2010  are working fine with my VBA code.


    Batkis

    Thursday, June 8, 2017 12:53 PM