VBA Pick up value from a declared Const variable

[AliCat]

Hi Guys,
Can someone help me to get the variable from a declared Const into my proc. The "hKey" does not pick up the Const value in the line "oReg.EnumKey hKey, rPath, arrSubKeys"
This code runs fine when I hard code the value in the proc and also works ok when I use the value "Software\Microsoft" in cell C4. I want this to be more interactive by entering "HKCU" in cell C3 but this is a stumbling block as it doesn't pick up the value "&H80000001".
Is there an orthodox way to achieve this.
Thanks.

Const HKCR = &H80000000
Const HKCU = &H80000001
Const HKLM = &H80000002


Sub RegQ()
    Dim oReg As Object
    Dim strComputer As String
    Dim hKey As String
    Dim rPath As String
    Dim arrSubKeys()
    Dim strAsk

    r = Selection.Row: c = Selection.Column
    strComputer = "."
    Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
    strComputer & "\root\default:StdRegProv")
    
    hKey = Selection.Offset(-1, 0).Value        ' Say:  HKCU  in C3
    rPath = Selection.Value & "\"       ' Say:  Software\Microsoft  in  C4
    oReg.EnumKey hKey, rPath, arrSubKeys
    
    r = r + 1
    For Each strAsk In arrSubKeys
        Cells(r, c) = strAsk
        Cells(r, c - 1) = rPath & strAsk
        r = r + 1
    Next

    Set oReg = Nothing
End Sub

 

[Norie]

You can't work with variables/constants like that in VBA.

All this code will do is put a string value in hKey.

hKey = Selection.Offset(-1, 0).Value

 

[AliCat]

Thanks very much for your reply.

oReg.EnumKey HKCU, rPath, arrSubKeys

Within the aforementioned script changing the line above with HKCU hard coded in produces the desired results.
So I take it from what you posted that it is not possible to have any interactivity with the "HKCU" part and that one solution might be to have separate scripts with an initial script that would pass control to the relevant script based on an entry in cell C3.
Thanks again, much appreciated.

 

[Norie]

You could use something like this to set the value of hKey based on the value in the cell.

Select Case Selection.Offset(-1, 0).Value
    Case "HKCR"
        hKey = HKCR
    Case "HKCU"
        hKey = HKCU
    Case "HKLM"
        hKey = HKLM
End Select

Not very practical though, especially with everything being hard-coded.

Another approach might be to set up a collection, or even store the values for the constants on a sheet.

 

[AliCat]

Thanks Norie,
We were on the same wavelength there. I'd re-written the code with the Select Case just prior to reading your last post. I think that seems the best solution to avoid having another list of data elsewhere in the workbook.
I'll look into the Collection approach and see what that has to offer.
It's good to get replies that tell you that your barking up the wrong tree.
Thanks again.