Excel VBA passing a Handle as a variable to an API function (Special Folders)

[AliCat]

Can anyone help me in passing handle values to a function as a variable.
This is to get the paths of all the special folders.
The undernoted code works perfectly well:

Private Declare Function GetFolderPath Lib "shfolder.dll" _
    Alias "SHGetFolderPathA" _
    (ByVal hwndOwner As Long, _
    ByVal nFolder As Long, _
    ByVal hToken As Long, _
    ByVal dwReserved As Long, _
    ByVal lpszPath As String) As Long

Sub GetFldPath()
    Dim sBuffer As String
    sBuffer = Space$(260)
    For Each sel in Selection
        If GetFolderPath(&H5&, &H5&, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
            sel.Offset(0, 3).Value = Left$(sBuffer, StrPtr(sBuffer))
        End If
    Next
End Sub

However, when I convert it to loop through entries I have in the worksheet, I get a type mismatch runtime 13 in the line containing the handle variable (hnd). I've tried various conversions such as CLng() as it is picking up entries in the column like &H1& &H2& ... &H5&. Is there a special way of passing a variable here.

Sub GetFldPath()
    Dim sBuffer As String
    'Dim hnd As Long      ' this line doesn't appear to help either way
    sBuffer = Space$(260)
    For Each sel In Selection
        hnd = sel.Value
        'MsgBox hnd   ' this display all entries ok when enacted
        '// To get the Path of each entry in the selection
        If GetFolderPath(hnd, hnd, -1, SHGFP_TYPE_default, sBuffer) = 0 Then 'type mismatch here
           sel.Offset(0, 3).Value = Left$(sBuffer, StrPtr(sBuffer))
        End If
    Next
End Sub

All help greatly appreciated. Thanks.

 

[John_w]

Try the cell values as &H1, &H2, etc, without the & suffix. The & suffix on a constant value or variable name in VBA code denotes a Long data type, however in your case &H1& is a string and VBA doesn't recognise the & suffix when it converts that string to a number (the hnd Long variable), hence the Type mismatch error.

 

[AliCat]

Thanks John_W.
I converted all my keys leaving out the trailing "&" and it worked a treat.