8 Replies Latest reply on Jan 6, 2019 7:14 AM by Ioannis Skarlatakis

    DotNetTools for VBA: a VBA Tool for Community

    Amen Allah Jlili

      I'd like to share a tool with the API community. For the past few years, I have developed many SOLIDWORKS addins and standlones. As a .NET Developer, things become a little more verbose when you move back to VBA for the purpose of rapid prototypes. DotNetTools is a COM library that exposes some of the rich functionnalities VBA desperately needs from a .NET developer prospective. This tool is meant for you guys so your feedback will be extremely appreciated!


      The DotNetTools expose some static methods of  System.IO.Path, System.IO.Directory, System.IO.File, System.DateTime, System.String and more.



      I understand that everything can be accomplished with VBA without resorting to this but having it on centralized library and preserving the .NET method signatures can be greatly appreciated.


      I'll throw in 2 examples:

      1. Browsing to a folder in VBA is not straightforward. You can do this with VBA. You need to call the Windows API in order to use the Windows native browse to folder dialog. This can be accomplished in this following way:

      Option Explicit
      Private Const BIF_RETURNONLYFSDIRS As Long = &H1
      Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
      Private Const BIF_RETURNFSANCESTORS As Long = &H8
      Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
      Private Const BIF_BROWSEFORPRINTER As Long = &H2000
      Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
      Private Const MAX_PATH As Long = 260
      Function BrowseFolder(Optional Caption As String, _
       Optional InitialFolder As String) As String
      Dim SH As Shell32.Shell
      Dim F As Shell32.Folder
      Set SH = New Shell32.Shell
      Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, InitialFolder)
      If Not F Is Nothing Then
          BrowseFolder = F.Items.Item.Path
      End If
      End Function
      Sub main()
      Path = BrowseFolder()
      End Sub


      While this may be easy for advanced developers, this may look intimidating for new developers or occassional macro users.

      With DotNetTools, we can accomplish that in a few lines

      Sub Main()
      Dim Tools as CADHero.DotNetTools
      Set Tools = new CADHero.DotNetTools
      Debug.Print Tools.BrowseTools.BrowseToFolder
      End Sub

      2. Working with Date and Time has never been this easy. You can even parse string dates into a culture aware VBA Date object.

      Sub main()
      Dim Tools As CADHero.DotNETTools: Set Tools = New CADHero.DotNETTools
      Dim DateTimeStr As String: DateTimeStr = "1/2/2018"
      Dim DateTime As Date
      With Tools.DateTimeTools
        DateTime = .ParseCultureAware(DateTimeStr, Culture_US)
      Debug.Print "US Format: " + .ToLongDateString(DateTime)
        DateTime = .ParseCultureAware(DateTimeStr, Culture_France)
      Debug.Print "French Format: " + .ToLongDateString(DateTime)
       DateTime = .AddHours(DateTime, 25)
      Debug.Print "Added 25 hours: " + .ToLongDateString(DateTime)
      Dim Year As Integer: Year = .GetYear(DateTime)
      Debug.Print "Year " + CStr(Year) + " is leap ? = " + CStr(.IsLeapYear(Year))
      End With
      End Sub
      'US Format: Tuesday, January 2, 2018
      'French Format: Thursday, February 1, 2018
      'Added 25 hours: Friday, February 2, 2018
      'Year 2018 is leap ? = False




      You can download the msi binaries off my DROPBOX. This should install two files called:

      CADHero.dll and CADHero.tlb.

      When you reference the tlb file in your VBA, if you face an error message says the ActiveX component couldn't be found. Please do the following:


      Run RegAsm.exe on the CADHero.dll to register the types in your machine:Open your command prompt as administrator

      Type cd c:\Windows\Microsoft.NET\Framework64\v4.0.30319  (Framework if Windows 32 bits)Command Prompt will change to c:\Windows\Microsoft.NET\Framework64\v4.0.30319>

      Now Type RegAsm.exe /codebase <Path_To_CADHero.dll>You’ll be prompted by a warning message saying the assembly is not signed. What’s important is that you see the message saying that types have been registered.



      Reference the tlb file:


      List of methods as of 1.0.11:


      *  public interface IStringTools


              bool Contains(string Source, string Target);

              void Trim(string Source, char Character);

              void TrimStart(string Source, char Character);

              void TrimEnd(string Source, char Character);

              string ToLower(string Source);

              string ToUpper(string Source);

              bool StartsWith(string Source, string Target);

              bool EndsWith(string Source, string Target);

              int GetHashCode(string Source);

              int IndexOf(string Source, char value);      

              Char[] ToCharArray(string Source);

              int LastIndexOf(string Source, char value);


      *  public interface IBrowseTools


              string SaveFileDialog(string StartUpDirectory, string ExtensionFilter,string defaultFileName);

              string BrowseToFolder();


           public interface IDirectoryTools


                String[] GetAllFiles(string Path, string SearchPattern, bool AllDirectories);

                bool CreateDirectory(string Path);

                DateTime GetLastWriteTime(string Path);

                DateTime GetLastWriteTimeUtc(string Path);

                DateTime GetLastAccessTime(string Path);

                DateTime GetLastAccessTimeUtc(string Path);

                bool Exists(string Path);

                bool Delete(string Path);



           public interface IFileTools


              bool AppendAllLines(string Path, string[] contents);

              bool AppendAllText(string Path, string contents);

              string[] ReadAllText(string Path);

              bool Exists(string Path);

              bool Delete(string Path);


          public interface IPathTools


              string GetFileNameWithExtension(string Path);

              string ChangeExtension(string Path, string Extension);

              string GetExtension(string Path);

              string Combine(string[] Paths);

              string GetFullPath(string Path);

              char[] GetInvalidPathChars(string Path);

              string GetTempPath();

              bool HasExtension(string Path);

              bool IsPathRooted(string Path);

              string GetDirectoryName(string Path);

              string GetRandomFileName();      


          public interface IInternetTools


              bool DownloadFile(string DownloadURL, string Target);


      public interface IDateTimeTools


              int GetYear(DateTime DateTime);

              int GetDay(DateTime DateTime);

              int GetMonth(DateTime DateTime);

              int GetHour(DateTime DateTime);

              int GetSecond(DateTime DateTime);

              int GetMinute(DateTime DateTime); 

              DateTime AddYears(DateTime DateTime, int value);

              DateTime AddDays(DateTime DateTime, int value);

              DateTime AddMonths(DateTime DateTime, int value);

              DateTime AddHours(DateTime DateTime, int value);

              DateTime AddSeconds(DateTime DateTime, int value);

              DateTime AddMinutes(DateTime DateTime, int value);

              int DaysInMonth(int Year, int Month) ;

              DateTime UtcNow();

              DateTime Today();

              int Compare(DateTime t1, DateTime t2);

              bool IsLeapYear(int Year);

              DateTime TryParse(string s);

              DateTime ParseCultureAware(string DateTime, Culture culture);

              String ToShortDateString(DateTime DateTime);

              String ToLongDateString(DateTime DateTime);

              String ToShortTimeString(DateTime DateTime);

              String ToLongTimeString(DateTime DateTime);