GC Performance Issue of VBA Class

[Background] When I was processing some data using VBA, I noticed that if ~100k class objects were added into a Dictionary (Microsoft Scripting Runtime), the script would stuck for several seconds at the end. If the objects quantity were increased to ~160k, it will stuck for 10+ sec.

[Cause] After a few tests and searches on the Internet, I found the issue was most probably caused by GC (garbage collection) of VBA. The GC mechanism of VBA is that whenever the reference count of an object drops to zero, the object is destroyed. However, due to unknown reason, such mechanism seems to exhibit a time complexity of O(n²). That is, if the object quantity increases, the GC time will increase drastically.

Relationship between Time & Object#

To verify the issue, I wrote a small piece of code to cram certain quantity of myClass objects into a Collection (result as above; code at the end of article). From the chart, it is obvious that while the creating time is linear to object # (blue line), the destroying time is quadratic to object #. When object # reaches 300k, creating only takes 0.8 sec, but destroying takes near 12 sec.

[Other findings] Per further test, other findings include:

  • This issue only exists for class object, but not for data type (e.g. integer, string, UDT, etc.). This is because data type are not objects and the GC does not depend on “zero reference count”.
  • When the container is an object (e.g. Dictionary, Collection), the issue can be stably reproduced. When the container is an array, the reproduction of issue is not stable.
  • The issue only exists if the class objects are initialized. It doesn’t exist if the object is only declared. This can be easily understood: if the object is not initialized, no GC is involved.

[Workaround] As the issue is too foundamental, I could not find any good solutions. Workarounds include:

  • Avoid using Class. Use UDT (user-defined type) instead. However, there are too many demands not achievable with UDT.
  • Only initialize the objects where necessary.
  • Use other languages.

[Appendix] Test code

Option Explicit

Sub TestScrrunClassSpeed()
    Dim c As New Collection     'Scripting.Collection
    Dim rgIO As Range           'Range for input/output
    Dim arrIO As Variant        'Temporary array for input/output
    Dim i As Long               'Iterator
    Dim testnr%, qty As Long    'The # and object quantity of each test
    Dim t!(0 To 2)              'The start time, create time and destroy time
    'Read parameter from sheet: column A defines object quantity of each test
    Set rgIO = Sheets("Result").Range("A2:C31")
    arrIO = rgIO.Value
    'Do each test
    For testnr = LBound(arrIO) To UBound(arrIO)
        'Read object quantity and prepare
        qty = CLng(arrIO(testnr, 1))
        t(0) = Timer
        'Create class objects
        For i = 1 To qty
            c.Add New myClass
        Next i
        t(1) = Timer
        'Clear class objects
        Set c = Nothing
        t(2) = Timer
        'Calculate the time
        arrIO(testnr, 2) = (t(1) - t(0)) * 1000
        arrIO(testnr, 3) = (t(2) - t(1)) * 1000
    Next testnr
    'Write result to sheet
    rgIO.Value = arrIO
End Sub


Design Fail: Microsoft Office 2019 New UI

In Oct 2018, Microsoft released the new MS Office 2019. The overall UI design changed little, but this version could be the worst in the past decade.

1. Unseparated Tabs

Microsoft introduced Ribbons in Office 2007 (Win Vista) replacing traditional menus. A typical ribbon includes Tabs for navigating through different command groups, and Tools Area containing command buttons.


Tips for Using Microsoft Office

This post records fragmentary but important advice of Microsoft Office, and is updated continually.


Configuring IKEv2 VPN on Windows 10 Mobile with SD Card / NTFS Soft Link

As mentioned in the last post, due to some ridiculous changes by Microsoft, you cannot set up an IKEv2 VPN in Settings GUI on Windows 10 Mobile. Usually this can only be done with MDM.
However, some people on XDA-DevwfunWindowsCentral find solutions for this issue.


Tips for IKEv2 VPN (strongswan) with Certificate Authentication

This post does NOT provide full tutorial of setting-up IKEv2 VPN. Please refer to Vultr’s Guide for step-by-step tutorial.

Pure certificate authentication means certificates are used for both server & client authentication. No PSK (pre-shared key) is involved.
Pros: safer than weak password; can’t be easily shared by verbal; no need to share the server PSK; difficult to spoof the server.
Cons: more complicated to configure.

Most configuration tutorials generally use server PSK + client PSK (usually IKEv1) or server cert + client PSK (usually IKEv2). This is enough for own use or sharing with a few friends. However, for sharing with many people or within an organization, cert auth will be more convenient. Based on general settings, the following paragraphs elaborates how to setup cert auth and tips.


Installing ocserv (OpenConnect Server) on CentOS 7 with Certificate Authentication

Although the title reads CentOS 7, this post actually applies to most RedHat releases.
On CentOS 6, you have to compile ocserv manually and deal with many dependency issues. But on CentOS 7, the configuration is fairly easy.

Apply for server certificate

Generate a CSR

cd ~
openssl req -new -newkey rsa:4096 -sha256 -nodes -out server.csr -keyout server.key

In the following prompts, only Common Name is mandatory. Fill it with domain name. Other blanks are optional.
ECC certificate is not recommended, because AnyConnect always prompts unsafe site even if it’s issued by a trusted CA.

Get a certificate from WoSign/StartCom or other CA with the generated CSR.
If signing algorithms can be chosen, do select SHA-2 rather than SHA-1.

The “fast issuance” of WoSign only applies to paid customers. Free users have to go through manual review on weekdays. Therefore I put certificate application on the first step.


Install WP Plugin Via Webpage On CentOS7/SL7

RHEL7/CentOS7/SL7 is SELinux-enabled by default. Therefore, some valid operations on v6 may be denied on v7.
For example, if you install Apache from default repo and WordPress, you won’t be able to install plugin via webpage. Apache’s attempt to connect to ftp and ftp’s attempt to read other files are denied by SELinux.

The solution is to change some settings by typing the following commands:
setsebool -P httpd_can_connect_ftp=1
setsebool -P httpd_can_network_connect=1
setsebool -P ftpd_full_access=1
If your ftp uses passive mode, add this line as well:
setsebool -P ftpd_use_passive_mode=1
where -P means making this change permanent.

Of course you may set SELinux from enforcing to permissive by typing setenforce 0, but this is not recommended.


SSL Tutorial #4: Creating SAN Certificate and Self-signing It with Own CA

Subject Alternative Name (SAN) cert allows verifying different domains using a single cert. This is an extension to SSL certs.

1. Create config

Create a config file mydomain.conf


Tips on Using LFTP as FTPS Client

LFTP is a sophisticated client for various protocols (FTP, FTPS, HTTP, …, and even BitTorrent).

Tip 1 – Basic usage


On Scientific Linux 6, you can easily install it from SL repo.
yum install lftp


Connect to FTPS with specific port, username and password.


Configure FTPS (FTP over SSL) for vsftpd on Scientific Linux 6

UPDATE (2013-05-06) This how-to does NOT work for WordPress plugin update.

There are mainly three forms of FTP:

  • FTP (File Transfer Protocol) – the original protocol. Very widely used, but not encrypted. It is becoming more and more vulnerable.
  • SFTP (SSH FTP) – using SSH to carry FTP transfer. Commands are very similar to FTP, but it is implemented under SSH protocol. The transfer is encrypted just as other SSH applications.
  • FTPS (FTP Secure) – FTP over SSL or TLS. Basically it is still FTP, but utilizes TLS/SSL for better safty. Login/data can be either or both encrypted.

WordPress update supports only FTP and FTPS. If you are not going to install SFTP plugins, then either you would risk exposing your account username and password, or you need to configure FTPS for account safety.