Aim: To create .Net Framework 4.8.1 COM Interop wrappers using C# to implement in VBA 64. This will enable various .Net Framework data types in VBA with early and/or late binding. Compatibility initially only VBA 64 on Windows as can only test on windows 64 bit of MS-Office. For Mac compatibility would require migrating to .Net Core.
Recommendations: Install the VBA Addin Rubberduck will allow easier navigation of virtual folders according to the rubberduck folder annotation.
Affected API due to VBA reserved words:
The API for the .Net class or VBA singletons for associated .Net classes may be required to be altered due to VBA reserved words. See reserved-word-list.
- TimeZoneInfo.Local renamed to TimeZoneInfo.Locale.
- String VBA singleton renamed to Strings.
- Array VBA singleton renamed to Arrays.
- Type VBA singleton renamed to Types.
As VBA doesnot have member overloading factory methods and member overloads will differ. Overloads generally are named with a preceeding number. Unique naming maybe used for factory methods.
Dependencies:
- DotNetLib.tlb type library
- mscorlib.tlb version 2.4 type library Eg. Windows\Microsoft.NET\Framework64\v4.0.30319\mscorlib.tlb
- VBIDE (Microsoft Visual Basic for Applications Extensibility 5.3) optional for the Rubberduck Export All Utility for exporting components.
- .NET Framework If it is not installed see Download .NET Framework
Usage:
- Register DotNetLib.tlb
- Either building the project in Visual Studio which registers the DotNetLib.tlb or run RegAsm.exe in administrator to register the type library DotNetLib.tlb.
- Currently manually installation and registration for type library DotNetLib.tlb See: register-dll
- Copy the DotNetLib.tlb files to a location which don't intend to change eg. C:\ProgramData\DotNetLib then register the DotNetLib type library
- Eg. To register C:\Windows\Microsoft.NET\Framework64\v4.0.30319\regasm.exe C:\ProgramData\DotNetLib\DotNetLib.dll /tlb
- Eg. To unregister C:\Windows\Microsoft.NET\Framework64\v4.0.30319\regasm.exe C:\ProgramData\DotNetLib\DotNetLib.dll /tlb /unregister
- If the files are moved will require to unregister and register manually.
- If the DotNetLib type library is updated will require to unregister and register manually.
- Add References required.
- Eg In MS-Access, MS-Excel see Tools->References
- For DotNetLibrary.accdb references may be required to be fixed by removing and adding back in.
- Add reference DotNetlib.tlb (Com Interlop wrappers of the .Net Framework 4.8.1) i.e. browse to location where stored
- Add reference mscorlib.tlb version 2.4
- Add reference Microsoft VBScript Regular Expressions 5.3 (Required only for the Rubberduck export utility not a core part of VBADotNetLib)
- The type libraries added can be viewed under View->Object Browser and select DotNetLib.tlb or using the Rubberduck Code Explorer.
- Add the VBADotNetLib VBA Factory/Singleton classes into a project.
- Either copy the classes or add a reference to project containing the classes.
- Recommended install Rubberduck VBA Addin.
For detailed explanation of the DotNetLib class members see netframework-4.8.1
Ms Access database VBADotNetLibrary.accdb VBA Factory classes and examples for the DotNetLib.tlb. Also a MS-Excel version VBADotNetLib.xlsm .
Note: The MS-Access contains the latest version of VBADotNetLibrary and examples as the development is performed in MS-Access and periodically exported to the VBADotNetLibrary MS-Excel spreadsheet.
Regular expressions : Converting strings containing escape sequences and special characterss:
To use escape and special characters. Note if require quotes " require to escape in VBA with double quotes.
VBA Example using Regex.Unescape with hexadecimal escape sequences
Dim stringUpper As String
stringUpper = "\x41\x42\x43" ' Create upper-case characters from their Unicode code units.
stringUpper = Regex.Unescape(stringUpper)
Debug.Print stringUpper
'Output: ABC
Issues:
Hashtable.Item(key) = valuetype causes an Object required error for value types. Added member SetValue(key,value) to use as an alternative until fixed.
- To fix requires creating an IDL and manually adding a propput for value types and compiling type library with MIDL.
ArrayList.Item(index) = item
-
Cannot assign value types using arraylist.Item(index) = valueType
-
Eg.
pvtStringList.Item(2) = "abcd"Will produce a Run-time error 424 Object required -
To cater for value types added the Arraylist.SetItem(index,item) member. Arraylist.SetItem(index,item) can be use for value or object types.
-
Eg. assigning a value type
pvtStringList.SetItem 2, "abcd" -
Currently List COM object wont allow to be created getting invalid use of New Keyword. This will removed and replaced with it's non-generic equivalent..
-
Too many things to do. Argh!
Things To do
- Unit testing using Rubberduck unit testing.
- Create an installer from Microsoft Visual Studio
Status: Latest Updates
Feb 4, 2024
- Added MatchEvaluator
- Added VBA MatchEvaluator singleton
Jan 21, 2024
- Added Uri
- Added Uri.cls VBA Singleton class
Jan 20, 2024 Updated DotNetLib.tlb Type Library
- Added Stopwatch
- Added Stopwatch.cls Singleton class for Stopwatch.
- Moved Type Library files to folder DotNetLib Type Library
- Removed StringFormatting.bas
Renamed the following:
- ArrayEx.cls to VBArray.cls
- VBAString.cls to VBString.cls
DoubleArray.cls Renamed the member DoubleArray.ToArray to DoubleArray.CreateInitialize1D
ObjectArray.cls Renamed the member ObjectArray.ToArray to ObjectArray.CreateInitialize1D
StringArray.cs Renamed the member StringArray.ToArray to StringArray.CreateInitialize1D
Updated all affected examples for renamed classes and members.
Updated DateTime, DateTimeOffset, TimeSpan, TimeZoneInfo examples Removed use of IDate, IDateTimeOffset, ITimespan, ITimeZoneInfo And use VBString.Format to format output for examples.
Update History
Classes initally focussing on are DateTime, DateTimeOffset, TimeSpan, TimeZoneInfo and associated classes.
Aug 29, 2023 Added: CultureInfo, DateTimeFormatInfo, NumberFormatInfo, TextInfo .
Sep 19, 2023 Added: ChineseLunisolarCalendar, GregorianCalendar, HebrewCalendar , HijriCalendar, JapaneseCalendar, JulianCalendar, KoreanCalendar, PersianCalendar, ThaiBuddhistCalendar, UmAlQuraCalendar
Sep 22, 2023 Added: CompareInfo
Sep 23, 2023 Added String For the VBA singleton wrapper renamed String to Strings due to VBA reserved word.
Sep 25, 2023 Added Regex Implemented so far Regex.Unescape and Regex.Escape
Sep 30, 2023 Added System.Text.RegularExpressions, Capture, CaptureCollection, Group, GroupCollection, Match, MatchCollection, Regex
Oct 3, 2023 Added ListString
- Attempted to dynamically create a List providing the data type instance however having issues casting, therefore will wrap a List for various basic types individually.
- Testing still to be done. Create, Add, BinarySearch, Contains, IndexOf, Insert, Reverse, Sort, appears functioning correctly.
Oct 5, 2023 Added ArrayList
Oct 12, 2023 Added Array, Type, GenericParameterAttributes
Oct 15, 2023 Added Queue, Stack
Oct 16, 2023 Added SortedList
Oct 17, 2023 Added CaseInsensitiveComparer, StringComparer
Oct 18, 2023 Added DictionaryEntry, Hashtable
Oct 31, 2023 Added StringBuilder
Nov 2, 2023 Added BitArray
Nov 16, 2023 Added Directory, DirectoryInfo, Environment, File, FileInfo, Path , FileSystemInfo, StreamWriter AccessControlSections, SpecialFolderOption, SpecialFolders, StringSplitOptions
Nov 23, 2023 Added ASCIIEncoding, Encoding, UnicodeEncoding, UTF32Encoding, UTF7Encoding, UTF8Encoding
Dec 27, 2023 Added RegionInfo
Jan 20, 2024 Added Stopwatch
Jan 21, 2024 Added Uri
DotNetLib Update September 30th, 2023
Added System.Text.RegularExpressions, Capture, CaptureCollection, Group, GroupCollection, Match, MatchCollection, Regex
Todo:
- Implement VBA singleton classes for Match and Group for static members.
- Examples and unit testing.
DotNetLib Update September 25th, 2023 Added Regex
- Implemented Regex.Unescape and Regex.Escape
- Regex.Unescape can be used to convert VBA literal strings containing escape characters.
Updated Strings, added the following members
- Compare, CompareOrdinal, Copy, Equals, IsNullOrEmpty, IsNullOrWhiteSpace
DotNetLib Update September 23rd, 2023
Added String
- So far only implemented static members String.Format
- Renamed String to Strings due to VBA reserved word.
Added: IFormatProviderExtension.cs to UnWrap IFormatProvider types.
DotNetLib Update September 22nd, 2023
- Renamed abstract class ICalendar to Calendar to keep consistent with Net Framework
- Updated VBADotNetLib for affected calendar classes and examples.
- Added CompareInfo, CultureInfo.CompareInfo member properties now availble.
- Todo add to VBADotNetLib CompareInfo singleton class.
DotNetLib Update September 20th, 2023
Updated DateTime.cs, IDateTime.cs, DateTimeSingleton, IDateTimeSingleton.cs,
- Added factory methods for ICalendar parameter.
- public DateTime CreateFromDate2(int pYear, int pMonth, int pDay, ICalendar calendar)
- public DateTime CreateFromDateTime2(int pYear, int pMonth, int pDay, int pHour, int pMinute, int pSecond, ICalendar calendar)
- public DateTime CreateFromDateTime3(int pYear, int pMonth, int pDay, int pHour, int pMinute, int pSecond, int pMillisecond, ICalendar calendar)
- public DateTime CreateFromDateTimeKind3(int pYear, int pMonth, int pDay, int pHour, int pMinute, int pSecond, int pMillisecond, ICalendar calendar, DateTimeKind pKind)
- DateTime.cls added the above new factory methods available from DateTimeSingleton DotNetLib.tlb.
Todo add examples and testing.
- Update DotNetLib class members that reference the Calendar class.
- Eg. DateTime constructors, DateTimeOffset constructors
- Updated DateTimeFormatInfo.Calendar member to use wrapped ICalendar. DateTimeFormatInfo.Calendar property should now be available to access and set. (Require to test)
DotNetLib Update September 19th, 2023
- Implemented abstract class Calendar as ICalendar, updated CultureInfo for properties Calendar and OptionalCalendars which are now availbable and added the following calendars:
Todo testing for creating each added calendar, CultureInfo.Calendar, CultureInfo.OptionalCalendars. Adhoc testing not detecting any missing Calendars required for the default Calendar or optional calendars.
- Update DateTimeFormatInfo.Calendar to use ICalendar
DotNetLib Update September 9th, 2023
- API changes for DateTime, DateTimeOffset, TimeSpan
- Merged member ToString4(string format, IFormatProvider provider) and replace with ToString2(string format, IFormatProvider provider = null)
- Updated examples using ToString4(string format, IFormatProvider provider) to use ToString2(string format, IFormatProvider provider = null) due to DotNetLib.tlb API changes.
- Add IComparable, IFormattable interfaces
- Added Console.cls Not fully functional (Work in progress)
DotNetLib Update September 5th, 2023
- Added TextInfo Class. Properties for CultureInfo.TextInfo now available.
- Added VBA wrapper TextInfo singleton class for TextInfo.ReadOnly(TextInfo) method.
- Todo update Ms-Excel VBADotNetLib.xlsm
DotNetLib Update September 3rd, 2023
- For DateTime and DateTimeOffset renamed DateOnly property to Date property to be consistent with .Net Framework.
- Updated all effected examples
DotNetLib Update September 2nd, 2023
- Fixed issues with DateTimeFormatInfo
- Changed format parameter to string from char
- public string[] GetAllDateTimePatterns(string format = null)
- public void SetAllDateTimePatterns([In] ref string[] patterns, string format)
- Added overloads for DateTime.GetDateTimeFormats
- Changed format parameter to string from char
- Refactored CultureInfo.cls renamed constructors to more meaningful names and combined overloads using an optional parameter.
DotNetLib Update September 1st, 2023
- Fixed issues with DateTimeFormatInfo
- When assigning an array to a property eg DateTimeFormatInfo.AbbreviatedDayNames Compile error: Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic
- https://stackoverflow.com/questions/13185159/how-to-pass-byte-arrays-as-udt-properties-from-vb6-vba-to-c-sharp-com-dll
- Added members to set the various arrays replacing the set propeterty which is no longer COM visibile.
- SetAbbreviatedDayNames([In] ref string[] abbreviatedDayNames)
- SetAbbreviatedMonthGenitiveNames([In] ref string[] abbreviatedMonthGenitiveNames)
- SetDayNames([In] ref string[] dayNames)
- SetMonthGenitiveNames([In] ref string[] monthGenitiveNames)
- SetMonthNames([In] ref string[] monthNames)
- SetShortestDayNames([In] ref string[] shortestDayNames)
DotNetLib Update August 29th, 2023
- Added DateTimeFormatInfo
- Added NumberFormatInfo
- DateTimeFormatInfo and NumberFormatInfo properties are now available for Cultureinfo
- Unit Testing required to test that various DateTime, DateTimeOffset, TimeSpan parsing functions using IFormatProvider is functioning correctly. Adhoc testing done using examples.
DotNetLib Version 1.2 Update August 17th, 2023
Completed rewritting the DotNetLib type library and VBA DotNetLib wrappers to use the Singleton pattern. Where factory methods and static members are in a singleton classs.
Currently the default interfaces IDateTime, IDateTimeOffset, ITimeSpan, ITimeZoneInfo, ICultureInfo for its corresponding COM object isn't displayed in the VBA Object browser or editor thou accessible. Can program either directly against the COM Object eg. Dim myDateTime as DotNetLib.DateTime or its interface Dim myDateTime as IDateTime
For the creation and access of static members use its corresponding Singleton/Factory class eg Set myDateTime = DateTime.CreateFromDate(2010, 8, 18)
Initial developement.
- API of the type library and VBA COM wrapper classes may be altered during initial development.
- Implemented the following C# COM Interlop wrappers of the .Net Framework 4.8.1 DotNetLib type library, see DotNetLib.tlb
- DateTime
- DateTimeOffset
- TimeSpan
- TimeZoneInfo
- CultureInfo
- DateTimeKind enum
- DayOfWeek enum
- TimeSpanStyles enum
- ReadOnlyCollection
- Adhoc testing using VBA examples located in DotNetLibrary.accdb
- VBA DotNetLib.tlb COM Wrappers implemented.
- CultureInfo
- DateTime adhoc testing and DateTime examples.
- DateTimeOffset adhoc testing and DateTimeOffset examples.
- TimeSpan adhoc testing and TimeSpan examples.
- TimeZoneInfo adhoc testing and TimeZoneInfo examples
- ReadOnlyCollection VBA wrapper to be implemented. DotNetLib.ReadOnlyCollection adhoc testing with TimeZoneInfo examples. TimeZoneInfo.GetSystemTimeZones returns a ReadOnlyCollection.
- Unit testing aim to do once VBA wrappers for COM objects implemented.
- Investigated auto generation of VBA COM object wrapper class. See: Refactor-COM-object-to-VBA-COM-wrapper-class
VBA Wrapper for ReadOnlyCollection
Implement interfaces in DotNetLib type library as work around for VBA Interface not showing property in watch window.
Development Notes
As COM Interlop doesn't support generic types required to convert or wrap to its non-generic equivalent.
How to treat generic types returned? eg. public static System.Collections.ObjectModel.ReadOnlyCollection GetSystemTimeZones()
DE0006: Non-generic collections shouldn't be used
System.Collections.Generic Namespace
Require to investigate how to correctly marshal arrays
-
pass-an-array-from-vba-to-c-sharp-using-com-interop
- DateTimeFormatInfo.AbbreviatedDayNames
- When attempting to assign an array to DateTimeFormatInfo.AbbreviatedDayNames Compile error: Function or interface marked as restricted, or the function uses an Automation typee not supported in Visual Basic
- https://stackoverflow.com/questions/13185159/how-to-pass-byte-arrays-as-udt-properties-from-vb6-vba-to-c-sharp-com-dll
- Fixed by implementing set methods and making set property not COM visible.
-
TimeZoneInfo.Local renamed member to Locale.
-
how-to-get-property-values-of-classes-that-implement-an-interface-in-the-locals
-
Work around implement interfaces required in the DotNetLib type library. They appear to work fine for type library interfaces but not VBA interfaces.
Currently List COM object wont allow to be created getting invalid use of New Keyword. This will removed and replaced with it's non-generic equivalent.
Will require implementing the following:
- Cultureinfo and associated classes. Implemented
- Calendar . Implemented, currently updating class members referencing the Calendar class.
- DateTimeFormat . Implemented.
- CompareInfo
- CultureTypes
- NumberFormatInfo . Implemented thou not tested or currently in use.
- DateTimeFormatInfo. Implemented not fully tested, may effect various DateTime parsing functions.
- TextInfo Class
VBA Wrapper for ReadOnlyCollection for SystemTimeZones i.e. of type TimeZoneInfo
Require to consider how to handle generic types in COM Interlop as not supported, possible work around implement each type separately, which enforces type safety.
Or replace with non-generic equivalent. To enforce type safety in VBA create a custom wrapper for the collection on the non-generic collection.
Collections List
How to create dynamic list? I.e. When creating a List specify the type required.