DotNetTools for VBA: a VBA Tool for Community

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

Installation

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 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);

    }

*/

SolidworksApi macros