1

Why does a string variable need to be enclosed in a cStr() conversion to be used as a key string for the CreateObject("WScript.Shell").SpecialFolders collection?

Demonstration of this absurdity(?):

Sub sdfgdfsg()
    Const strCon_SpecialFolderName As String = "MyDocuments"
    Dim strVar_SpecialFolderName As String
    strVar_SpecialFolderName = "MyDocuments"
    
    Debug.Print CreateObject("WScript.Shell").SpecialFolders(strCon_SpecialFolderName) ' CORRECT
    Debug.Print CreateObject("WScript.Shell").SpecialFolders(strVar_SpecialFolderName) ' WRONG! (it returns the Desktop path instead)
    Debug.Print CreateObject("WScript.Shell").SpecialFolders(CStr(strVar_SpecialFolderName)) ' CORRECT
    Debug.Print CreateObject("WScript.Shell").SpecialFolders("MyDocuments") ' CORRECT
End Sub

I read the documentation about the index argument for a collection without finding an answer.

6diegodiego9
  • 503
  • 3
  • 14
  • 1
    `Dim strVar_SpecialFolderName As Variant` fixes it, which does not really go well with the `CStr` solution. It's not indexing though, it's a method call. It was designed for scripting languages in the first place so it's no wonder it works with Variant, but then why would `CStr` also fix it? Probably has something to do with exactly how VBA marshals values on late bound calls. – GSerg Jun 09 '22 at 08:45
  • 1
    You can also solve the issue using `Debug.Print CreateObject("WScript.Shell").SpecialFolders((strVar_SpecialFolderName))`. It looks, placing the string between a new set of parenthesis, it evaluates it as it should. I (also) read about conversion to `Variant`, but I did not see any Microsoft statement on the issue... I used this way when passing the string variable to a function. In such a case, passing it `ByValue` also solves the problem. – FaneDuru Jun 09 '22 at 08:50
  • 2
    @FaneDuru Ah, another instance of https://stackoverflow.com/q/52686198/11683 then... – GSerg Jun 09 '22 at 08:53
  • @GSerg Yup..... – FaneDuru Jun 09 '22 at 08:56

1 Answers1

2

The WScript library was designed to be used from scripting languages such as VBScript which use Variants. The SpecialFolders.Item that you are calling expects a Variant containing a string, too.

The result you are seeing appears to come from the fact that the library is not able to read the Variant-wrapped string value VB passes, and does something wrong instead. You can achieve the same result with

Dim strVar_SpecialFolderName As String
strVar_SpecialFolderName = vbNullString

'Returns C:\Users\Public\Desktop
Debug.Print CreateObject("WScript.Shell").SpecialFolders(strVar_SpecialFolderName)

For reasons I don't fully understand, there sometimes is a problem with passing Variant-wrapped data from VBA to an external object. I speculate that it may have something to do with the presence or absence of the VT_BYREF flag in the Variant that VB(A) produces, and it produces it differently for constants, local variables and temporaries.

I believe this to be a problem on the receiving side, not in VBA.

Workarounds include:

  • Declaring the variable as Variant to begin with:

    Dim strVar_SpecialFolderName As Variant
strVar_SpecialFolderName = "MyDocuments"

Debug.Print CreateObject("WScript.Shell").SpecialFolders(strVar_SpecialFolderName)

  • Forcing an evaluation which turns the local variable into a temporary (that is what your CStr does, too) which apparently changes how VB packs it into a Variant:

    Dim strVar_SpecialFolderName As String
strVar_SpecialFolderName = "MyDocuments"

Debug.Print CreateObject("WScript.Shell").SpecialFolders((strVar_SpecialFolderName))
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Thanks @GSerg. In your linked documentation I read "for the [...] SpecialFolders collections, index is a string" so I can't even know in advance that there will be a string to variant conversion. Would you recommend using (one) workaround preventively each time we pass a string variable to an (any) external object wanting a string or variant? – 6diegodiego9 Jun 09 '22 at 10:15
  • @6diegodiego9 I wouldn't actually. If it's indeed a string, there is no VT_BYREF in the first place. And if it's a Variant, the receiving side must be able to cope with both, and it's a bug if it doesn't. So use as needed, and leave a comment when you do, because it's not a "normal" way of calling it – GSerg Jun 09 '22 at 10:30
  • Nice explanation(s) on a the described hypothesis and facts. Which looks bo be sustained by solid logic... – FaneDuru Jun 09 '22 at 11:02