Can anyone explain this behavior regarding string variable passing

[TheSmileyCoder]

I have this code:

Public Function SpecialFolderPath(strFolder As String) As String
 
    Dim objWSHShell As Object
    Dim strSpecialFolderPath
 
    'On Error GoTo ErrorHandler
    ' Create a shell object
    Set objWSHShell = CreateObject("WScript.Shell")
    ' Find out the path to the passed special folder,
    '  just change the "Desktop" for one of the other options
    SpecialFolderPath = objWSHShell.SpecialFolders(strFolder)
 
    ' Clean up
    Set objWSHShell = Nothing
    Exit Function
ErrorHandler:
 
    MsgBox "Error finding " & strSpecialFolder, vbCritical + vbOKOnly, "Error"
End Function

Now regardless of what I pass as argument (strFolder) I always get the same result:
C:\WINNT\Profiles\All Users\Desktop
Now If I change line 11 of the code to:

SpecialFolderPath = objWSHShell.SpecialFolders(strFolder & "")

I get the correct return value, depending on what I pass as strFolder argument.

Do anyone have a good explanation of why that is?

[NeoPa]

Appending an empty string has the effect of coercing a value to a string type. That's the only effect I can see. As the variable is already clearly declared as a string anyway, this effect does surprise me Smiley. To explore further I'd need more information on both the data being passed, as well as the code used to call the function. It's almost as though the calling code can somehow confuse the function into dealing with value that isn't a valid string. This is certainly a weird one on the face of it.

An attached database may make looking even easier. You know the drill with that, I'm sure, but for ease of reference I'll copy it below anyway.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
8. Compress the database into a ZIP file.
9. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[TheSmileyCoder]

Hi NeoPa and thank you for your reply.

I have uploaded a simple database designed to visualize the problem.

I remember once before having run into a similar situation, but can't remember where it was :P

[Rabbit]

That's odd because I have no problems with that code. Have you tried recreating it in a fresh database? Maybe it's just corruption.

[Lysander]

I could be really flash here and pretend I worked this out all by myself, but I cheated and googled the problem:)

I think that the reason for the 2 different results is that when you are doing:
wshshell.SpecialFolders(FolderString)
you are really pulling up the 'item' property.

wshshell.SpecialFolders.item(FolderString)

The argument in the 'item' property is a variant (legal values can be either a number or a string). When you send just a plain string variable it doesn't know what to do with it so it defaults to the 'desktop' special folder.

The following change to your code seems to work

SpecialFolderPath = objWSHShell.SpecialFolders.Item(CVar(strFolder))

Again from another site

For whatever odd reason it seems to try to evaluate the String value passed as an Integer, and gets 0.

I think the fact that this a not a function but a property returning a collection object confuses us when we look at it too.

Very disconcerting behavior all right.

All credit to dilettante and mjclare (whoever they are) for a post on another site in 2007

[NeoPa]

Wow! I didn't expect this to be a WSH (Windows Scripting Host) issue. Nice find Lysander.

I did a fair bit of scripting, particularly with AD (Active Directory) and the Registry, a few years back and I do recall that due to the flexible nature of values (Not only numeric versus string but also arrays and scalars, and even single entry arrays - just to make life ultra-complicated), it was necessary for me to write routines to set and get the values using a routine that determined the required type and converted to and from that type before use. All good fun.

Unfortunately, I no longer have access to any of that work, and it's been a couple of years since I did it so my recollections are hazy. I must visit my old workplace sometime and get the code I developed at least. It may not be any use to me directly, but it will at least provide me with reminders of good ways to approach things.

I was also impressed yet again Smiley, that you provided a properly prepared database with which to illustrate your problem. I probably won't need to do anything with it now, but I have a copy still, so can go back to it at need.

[TheSmileyCoder]

@Rabbit: The attached example IS a fresh db, created just for demo purposes.

@Lysander: Thank you for finding that for me. I am not 100% into the exact mechanics of using variants, since in 99.9% of cases I declare my variables as strings, or integers/longs/doubles. I am still surprised that the interpretation of a variant can lead to such issues. Anyways thank you.

@Neopa: After answering a question here and there, you get to the point where you properly realise how important the specifics of a question can be, especially one thats a bit more complicated then the average.

[NeoPa]

That's so true Smiley, not that your contributions could easily be dismissed as merely answering a question here and there mind.