VBA declare undefined type variable?

Question

Thanks for reading this.

I'm working on a personal project. I wrote a script that adds Scripting Runtime (to use Dictionary type), then declares a variable such as DictSummary as Scripting.Dictionary. The problem is, VBA does not allow this as it scans the code for error even before running it. When the Scripting Runtime is not added, the Dim DictSummary as Scripting.Dictrionary yields "User-defined type not defined" error.

If ReferenceIsAdded("Scripting") = False Then
    If ProjectIsNA Then
        Exit Function
    Else
        ThisWorkbook.VBProject.References.AddFromFile "C:\Windows\System32\scrrun.dll"
    End If
Dim DictSummary As Scripting.Dictionary

I tried adding a Function that declares, assigns, and returns a variable of Dictionary type, to avoid this error.

Public Function AddADict() As Variant
    Dim myDict As Scripting.Dictionary: Set myDict = New Scripting.Dictionary
    Set AddADict = myDict
End Function

Then I get the variable like this

Set DictSummary = AddAdict()

It does avoid that error but the returned variable is not quite like that, the type of DictSummary is Variant/Object/Dictionary, while it should be Dictionary/Dictionary for that variable to work. I tried On Error Resume Next but it does not help.

Is there a way to avoid that, or is there a way to optionally declare a variable?

If it is a personal project, why not just add the reference manually? It takes about 10 seconds. If it isn't a personal project, just use late binding Dim myDict As Object: Set myDict = CreateObject("Scripting.Dictionary"). VBA does have some support for conditional compilation, but I don't think that it would help you here. — John Coleman
– John Coleman, Commented Jan 27, 2019 at 13:56
@SMeaden Functionally it would work fine -- but it wouldn't hook into Intellisence (which I am guessing makes OP unhappy with this somewhat odd way of late binding). — John Coleman
– John Coleman, Commented Jan 27, 2019 at 15:05
@JohnColeman thanks for the late binding tips, I read it somewhere but don't have sense that it would work here. Also the Variant/Object/Dictionary works fine, I just made stupid code so it appears "not working" — Solus161
– Solus161, Commented Jan 27, 2019 at 16:09

S Meaden · Accepted Answer · 2019-01-27 15:12:19Z

1

Do you protest at the Variant because you could define your function to return an Object. Running the sub TestAddDict below the variable objDict can be seen in the Locals Window to be of type Object/Dictionary

Option Explicit

Function AddADict() As Object
    Set AddADict = VBA.CreateObject("Scripting.Dictionary")
End Function

Sub TestAddADict()
    Dim objDict As Object
    Set objDict = AddADict
End Sub

answered Jan 27, 2019 at 15:12

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Solus161 Over a year ago

Holly**** I didn't even think about returning an object, I'm pretty new to this. Thanks anyway, both Variant and Object works.

Collectives™ on Stack Overflow

VBA declare undefined type variable?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related