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.

CodePudding user response：

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))