Month: March 2024

Exploring VBA (Visual Basic for Applications) can sometimes feel like finding your way through a complex puzzle, especially when working with the Microsoft Shell Controls and Automation reference. In this blog post, we'll explore an unusual behavior. Problem: We've noticed that Shell.App throwing an error in case of using string variable in late binding. That might puzzle even experienced VBA users. Test Case: To taste the issues we have tried to count how many files are there in a specific folder path using ShellApp. If you prefer to see a full video of this issue, then you can check this video link here:- Shell.Application Trap Exposed. Primarily we have run the test using early binding method and it works correctly in both cases whether it is using Constant to declare the file path or a string Variable. As usual, it prints the correct result in both cases. Private Sub TestEarlyBindingConst() Const TEST_FOLDER As String = "D:\Downloads" Dim ShellApp As Shell32.Shell Set ShellApp = New Shell32.Shell Dim SourceItems As Shell32.FolderItems Set SourceItems = ShellApp.Namespace(TEST_FOLDER).Items Debug.Print SourceItems.Count End Sub Private Sub TestEarlyBindingVar() Dim TEST_FOLDER As String TEST_FOLDER = "D:\Downloads" Dim ShellApp As Shell32.Shell Set ShellApp = New Shell32.Shell Dim SourceItems As Shell32.FolderItems Set SourceItems = ShellApp.Namespace(TEST_FOLDER).Items Debug.Print SourceItems.Count End Sub Then we have tried the same test using late binding. And in case of Constant, it works correctly. Private Sub TestLateBoundConst() Const TEST_FOLDER As String = "D:\Downloads" Dim ShellApp As Object Set ShellApp = CreateObject("Shell.Application") Dim SourceItems As Object Set SourceItems = ShellApp.Namespace(TEST_FOLDER).Items Debug.Print SourceItems.Count End Sub But when we run the test where we have declared the folder path as a string variable and then use the variable, it throws an error. Private Sub TestLateBoundVar() Dim TEST_FOLDER As String TEST_FOLDER = "D:\Downloads" Dim ShellApp As Object Set ShellApp = CreateObject("Shell.Application") Dim SourceItems As Object Set SourceItems = ShellApp.Namespace(TEST_FOLDER).Items Debug.Print SourceItems.Count End Sub The Error is And the issue is on that line of code. But the same code works perfectly in the case of constant. Findings: So we have gone through a trial and error process and found a solution to this weird behavior where we have used empty string before and after the TEST_FOLDER parameter Or vbNullString. Then the code runs perfectly. Private Sub TestLateBoundVarWithEmptyString() Dim TEST_FOLDER As String TEST_FOLDER = "D:\Downloads" Dim ShellApp As Object Set ShellApp = CreateObject("Shell.Application") Dim SourceItems As Object Set SourceItems = ShellApp.Namespace("" & TEST_FOLDER & "").Items Debug.Print SourceItems.Count End Sub We think you may have noticed this weird behavior of the Microsoft Shell Controls and Automation reference. We've set out to explore the unusual aspects of late binding with the Microsoft Shell Controls And Automation reference in VBA. By persistently investigating and staying determined, we've discovered valuable insights that will surely enhance our coding skills in the future. You are welcomed to share your thoughts about this post in the comment section.

Excel VBA Programming – The Complete Guide

Excel

Excel VBA & Scripts

Formulas

Power Query

Python

Outlook + Excel VBA

Others

Free Guide

The ONE Critical Excel Skill You Need

Month: March 2024

Shell.Application Weird Behavior: Prevent Debugging Headaches Now!

Excel VBA Programming – The Complete Guide

Free Guide

The ONE Critical Excel Skill You Need

Stay Ahead with Weekly Insights!

Dive into Excel, Al and other essential tech news:

carefully crafted for the modern professional.