Smart Indenter and Excel 2013

I have recently installed Office 365 on a new computer running Windows 8.1 (actually the third one in just over a month, but that’s another story).  Office 2013 has several annoying features, but for me the most annoying was that the VBA smart indenter add-in from Office Automation did not work.  The web-site only mentions Excel versions up to 2003, but it was working with no problems with Office 365 and Excel 2013 on my last Windows 7 computer, and a search suggests that it should work with Windows 8 and 32 bit versions of Office 2013, but on my machine it doesn’t.

My solution to this problem was to install the alternative smart indenter from Andrew Engwirda.  This works on my system with no problems (after reading the installation instructions, you need to enable “Trust access to the VBA project object model” ), and will also work with 64 bit versions of Office.

If anyone else has had a problem with the original code indenter and 32 bit Office, I’d be glad to hear from you, especially if you found a way to make it work, but in the meantime Andrew’s alternative code works just fine.

Advertisements
This entry was posted in Computing - general, Excel, VBA and tagged , , , , , . Bookmark the permalink.

23 Responses to Smart Indenter and Excel 2013

  1. Gary says:

    I use smart indenter on the standalone version of excel 2013. di you try right clicking and installing as administrator?

    Like

  2. Tony M says:

    I came on here to say yes it works for me, only to find it’s ok on my work PC but I can’t make it work on the home one … can’t trace any difference at the moment.

    Like

  3. Kamolga says:

    I try to install it on office 365 64 bits without success (stored in C/programs/Microsoft office 15/root/office15/addin). I can see it in VBA project, went to the trust center to trust VBA object but still nothing when I right click a module or a code. I see it in developper tab add in but not in VBA add in manager.

    Like

  4. jonpeltier says:

    The Smart Indenter has been working for me in all 32-bit versions of Office up to and including 2013 that I have been running. However, I just installed 2016 on a new laptop, and while Smart Indenter seems fine, it ignores my custom tab width of 2 spaces, using instead the default 4 spaces.

    Like

    • jonpeltier says:

      Hmm, it’s also using the default tab width in Excel 2013. Something must have goofed up when I set up this computer.

      Like

    • jonpeltier says:

      Aha, the Smart Indenter uses the tab width setting that is stored in the registry here:
      HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\6.0\Common\TabWidth

      But later versions of Office store the custom tab setting in
      HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\6.0\Common\TabWidth
      or
      HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\6.0\Common\TabWidth

      So I had to recreate the first key and insert the custom tab width. Now it works fine.

      Like

      • jonpeltier says:

        AAARGH! Clicked SEND too soon. That last comment should read:

        Aha, the Smart Indenter uses the tab width setting that is stored in the registry here:
        HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\6.0\Common\TabWidth

        But later versions of Office store the custom tab setting in
        HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.0\Common\TabWidth
        or
        HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.1\Common\TabWidth

        So I had to recreate the first key and insert the custom tab width. Now it works fine.

        Like

      • dougaj4 says:

        Thanks for that Jon. (Although since I have been playing with Python I have got into the habit of indenting as I go anyway).

        Like

      • Jon Peltier says:

        Oh, I indent as I go. But then whenever I make changes, or drop in code from another project, I may need to adjust a bunch of indenting all at once.

        Like

  5. bIOforger says:

    I can’t get it to work at all in Excel 2016. The addin is there, but the context menu doesn’t show in the code window in VBA.

    Like

  6. Jeff S. says:

    Was able to get this working of Win10(x64) Office 2013(x32). The issue was with Msaddndr.dll that VBA indenter seems to reference. I had an older version installed and once I updated and registered the proper version, Indenter installed perfectly (or you can manually register the DLL). Here is the related MS KB article: https://support.microsoft.com/en-us/kb/2792179
    My issue was reflected in the install log @ C:\Program Files (x86)\Office Automation\Smart Indenter\VBA6\INSTALL.LOG, “Could not Self-Register: C:\PROGRA~2\OFFICE~1\SMARTI~1\VBA6\INDENT~1.DLL”
    Hope this helps anyone in the same situation.

    Like

  7. Daniel S. says:

    Had the same issue with smartindenter.
    on my Win10/Excel2013 it was working on an other Win7/Excel2013 it did not install correct. (Could not Self-Register: C:\PROGRA~2\OFFICE~1\SMARTI~1\VBA6\INDENT~1.DLL)

    My solution was to copy the dll (“c:\Program Files (x86)\Common Files\DESIGNER\MSADDNDR.DLL”) from the running system to the other and registering it manually.
    regsvr32 “c:\Program Files (x86)\Common Files\DESIGNER\MSADDNDR.DLL”

    after that smart indenter installed correct.

    Like

  8. PhilUK says:

    It seems the problem may be connected to the installation of MZ Tools…
    http://www.devhut.net/2015/01/12/smart-inderter-not-working-in-office-2010-2013/

    Like

    • jonpeltier says:

      I’ve never had a problem with MZ Tools interfering with Smart Indent. I’ve been using them together for ages.

      Like

      • dougaj4 says:

        Thanks Jon,
        I have just re-installed Smart Indent (now with 32 bit Excel 2016 and windows 10), and this time it works with no problem, along with MZ Tools.
        I don’t know what has changed, but it installed with no problems at all.

        Like

      • dougaj4 says:

        Checking PhilUK’s link, it doesn’t say MZ Tools is a problem, it says it needs to be installed first, and in fact Smart Indent by itself didn’t work.
        I had MZ Tools when I installed Smart Indent today, and it may not have been installed when I tried last year, so this is consistent with my experience.
        Some of the comments at the link say it didn’t work for them however, and refer to msaddndr.dll, as recommended in previous comments here.

        Like

    • dougaj4 says:

      Thanks PhilUK, this (and Jon Peltier’s comment) prompted me to have another go, and today it works.

      Inverted Windows Haiku:
      Yesterday it worked.
      Today it is not working.
      Windows is like that.

      Like

  9. Jacky says:

    Hi !

    This morning, I installed Office 2013 and after installing Smart Identer, this one was inoperative. Due to research, I came across a site where he was talking about a DLL that could fix the problem. This is what I did, downloaded and installed. I put in PJ this dll + Smart Identer, that will have to put in the folder System32 for windows 32bits; In the SysWOW64 folder for 64-bit Windows. Then, open the command prompt (admin), and then

    For 32bit (copy and paste):
    % Systemroot% \ System32 \ regsvr32 C: \ Windows \ System32 \ MSADDNDR.dll

    For the 64bit:
    % Systemroot% \ SysWoW64 \ regsvr32 C: \ Windows \ SysWOW64 \ MSADDNDR.dll

    Once installed, install the Smart Identer.

    There you go. You can finally use it.

    Download here: https://drive.google.com/drive/folders/0BxkhbRLAoge_enBubVduZnhLN1U

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s