Passing variables in cells to macros

Hi all,

I have a very simple function showing the duration of external audio files:

Function WaveLength(sPathWaves As Variant, sFilename As Variant)
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(sPathWaves)
    Set objFolderItem = objFolder.ParseName(sFilename)
    
    WaveLength = objFolder.GetDetailsOf(objFolderItem, 27)
End Function

I can call the function like this:

=Personal.xlsb!wavelength("D:\Temp";A1)

where D:\Temp is the path and A1 refers to a cell containing the file name.

This works too.

However when I replace "D:\Temp" with prefix (the name of a cell containing the same text), things go wrong and I get #VALUE! instead. I think this has to do with the \, but I'm not sure.

I'd like to be able to call the function like:

=Personal.xlsb!wavelength(prefix;A1)

Any suggestions?

No one knows?

It's not the kind of programming I do, so I don't understand why this would be so. When you pass an cell to a UDF, the default is to pass it by reference. When the argument is a variant data type, this usually means that the variable essentially becomes a range object specifying that particular cell. Most of the time, when we use the argument name, VBA can correctly assume that we mean "the value contained in cell argument." In this case, I'm not sure that is true. I tried something that recognized the arguments as "range objects" then used the value property to explicitly tell VBA to use the value of the cell.

Set objFolder = objShell.Namespace(sPathWaves.value)
    Set objFolderItem = objFolder.ParseName(sFilename.value)

This seemed to work for me.

I'm sorry, I completely overlooked this answer. Thank you very much, MrShorty!