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 we've noticed with the Shell.App object, shining a light on the tricky details that might puzzle even experienced VBA users. To taste the issues we have tried to count how many files are there in a specific folder path using ShellApp. 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 Message is " Object variable or with block variable not set ". And the issue is on that line of code. Set SourceItems = ShellApp.Namespace(TEST_FOLDER).Items But the same code works perfectly in the case of constant. 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 welcome to share your thoughts about this post in the comment section.

Month: March 2024

Shell.Application Weird Behavior: Prevent Debugging Headaches Now!

Copyright © 2022 | Md. Ismail Hosen

Freelance VBA Developer

Software Engineer, XYZ Cloud BD Ltd.

Quick Links

Resources

Support