AnsweredAssumed Answered

DotNetTools for VBA: a VBA Tool for Community

Question asked by Amen Allah Jlili on Oct 9, 2017
Latest reply on Jan 31, 2019 by 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_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);