Changing a registry key using a vba macro 1

[Chrissirhc]

Hello,

How can I change a registry key using an Excel VBA macro?

I wanted to put this code in auto_open so that the registry has correct information for the sheet to function.

Thanks in advance,

Chris

 

[TonyJollans]

Hi Chris,

It depends a bit.

For VBA-specific settings you can use SaveSetting. For general registry values you can use System.PrivateProfileString or some API calls.

But, I don't really understand why you are asking. If your Workbook needs data in the registry in order to work then does it not already have registry code in it? But, more to the point, putting values into the registry on open for use within a session is a completely wrong use of the registry; you would only normally want to use it to save values across sessions.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Chrissirhc]

I'll clarify my needs:

Registry entry HKEY_CURRENT_USER\Softare\Place\Specialsetting\MaxNum must be 50000 for the sheet to work.

Normally that value is 1500. When a new user wants to use the sheet someone has to manually enter that key. Instead I want the sheet to check if its correct and if not put it in the registry.

I've found some code to do it using an API, but I can't seem to do the windows API declaration...

I'm using "Private declare function RegOpenKeyA "ADVAPI32.DLL" (ByVal hkey as Long, ByVal sSubkey as String, _
ByRef hkeyResult as Long) as Long"

But that doesn't compile...

Any ideas?

Thanks in advance,

Chris

 

[TonyJollans]

Well, this will do it:

System.PrivateProfileString("", "HKEY_CURRENT_USER\Softare\Place\Specialsetting", "MaxNum") = 50000

but I have to say that I can't think of any circumstances where this is the right solution to a problem - although it could certainly be a quick and dirty fix

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Chrissirhc]

Will that write to the registry or just work for the session?

Also can I read from the registry using the system object?

I got the above to compile I missed a keyword "lib"... I don't like the feeling of having to use dlls like this, but for some reason I can't use tools --> references to add the dll. Any ideas?

 

[TonyJollans]

Yes that will write, permanently (there is no other way), to the registry.
To read from it, use:

yourVar = System.PrivateProfileString("", "HKEY_CURRENT_USER\Softare\Place\Specialsetting", "MaxNum")

There should be no problem using the API as you have posted. What don't you like about it?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[KenPuls]

Hello,

John Walkenbach has a registry wrapper function for both reading from and writing to the registry. Works very well. The only thing is that it was published in his Excel Power Programming books, and not on his website. If you do any amount of programming in Excel, though, the book is a great buy, and I'd highly recommend it.

Ken Puls, CMA

http://www.excelguru.ca

 

[Chrissirhc]

What reference do I need to add for "System.PrivateProfileString" to work?

Also I have the John Walkenbach book... That is where I got the code I posted above.

Thanks a lot,

Chris

 

[Chrissirhc]

And another quick question: Why can't I access the windows API using tools references rather than "Private declare function RegOpenKeyA "ADVAPI32.DLL" (....." ??

Thanks,

Chris

 

[TonyJollans]

My deepest apologies, the System Object is in the Word library and not natively available in Excel. In Excel you'll have to use the API - or reference Word but that's a bit excessive

I daresay someone can give you a fuller explanation but the routines accessible via the Windows API are not exposed as an Object Model so can't be referenced as one. They're just different, that's all.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Chrissirhc]

No problem Tony...

I've also seen these Excel functions getsetting(....)

However I found this statement on the web

"This recipe is for reading/writing keys local to your application -- this is to provide persistent settings for your VBA app. It does not cover arbitrary access to the Registry (i.e. looking at any key)."

Can anyone translate into layman terms? Does it mean you can only access Microsoft applications registry settings?

Thanks,

Chris

 

[Loomah]

If memory serves it gives access to a special area of the registry set aside for VBA apps. Not sure what the full key path is but have you checked on line help for the function?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Chrissirhc]

Yes I checked help.

 

[TonyJollans]

Yes I checked help.

And ... did you find the answer or not?

Loomah is correct - GetSetting and SaveSetting are not general purpose registry access functions and won't help you at the moment; they only access specific registry keys.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Chrissirhc]

Can someone explain in more detail but very simply "I daresay someone can give you a fuller explanation but the routines accessible via the Windows API are not exposed as an Object Model so can't be referenced as one. They're just different, that's all. "

I don't really understand that. I can browse to other dll's and its fine. Does the dll have to be built specifically for Excel so that you can tools references?

Cheers,

Chris

 

[KenPuls]

Hi Chris,

The GetSetting and SaveSetting functions will place/retrieve data from KEY_CURRENT_USER\Software\VB and VBA Program Settings\. You get the ability to create a folder in that key, and create/modify and keys in there to do what you need. Those functions cannot write outside that main key, though.

To do what you need, you'll have to use the API route. I'm not sure what version of J-Walk's book you have, but in the 2002 version, the Registry Wrapper functions are in Chapter 11, under the subheading "Reading from and writing to the Registry". In the 2002 version that I have, it starts at page 350. I'll provide you with the API Calls here, and a routine to use his functions, but I can't in post his routines though.

Make sure you have the following 5 API calls at the top of your module:

'32-bit API declarations required for GetRegistry & WriteRegistry functions
Private Declare Function RegOpenKeyA Lib "ADVAPI32.DLL" _
    (ByVal hKey As Long, ByVal sSubKey As String, _
    ByRef hkeyResult As Long) As Long

Private Declare Function RegCloseKey Lib "ADVAPI32.DLL" _
    (ByVal hKey As Long) As Long

Private Declare Function RegSetValueExA Lib "ADVAPI32.DLL" _
    (ByVal hKey As Long, ByVal sValueName As String, _
    ByVal dwReserved As Long, ByVal dwType As Long, _
    ByVal sValue As String, ByVal dwSize As Long) As Long

Private Declare Function RegCreateKeyA Lib "ADVAPI32.DLL" _
    (ByVal hKey As Long, ByVal sSubKey As String, _
    ByRef hkeyResult As Long) As Long

Private Declare Function RegQueryValueExA Lib "ADVAPI32.DLL" _
    (ByVal hKey As Long, ByVal sValueName As String, _
    ByVal dwReserved As Long, ByRef lValueType As Long, _
    ByVal sValue As String, ByRef lResultLen As Long) As Long

Then copy in his GetRegistry and WriteRegistry functions. At that point, the following should allow you to update the key with the correct value:

Sub CreateKey()
    Dim RootKey As String
    Dim Path As String
    Dim RegEntry As String
    Dim RegVal As Boolean
    Dim MaxNum As Long

    RootKey = "HKEY_CURRENT_USER"
    Path = "Softare\Place\Specialsetting"
    RegEntry = "MaxNum"
    MaxNum = 50000

    Select Case WriteRegistry(RootKey, Path, RegEntry, RegVal)
        Case True
            MsgBox "Value updated!", _
                    vbOKOnly + vbInformation, "Success!"
        Case False
            MsgBox "An error occured writing to the registry." & _
                    vbCritical + vbOKOnly, "Error!"
    End Select
End Sub

HTH,

Ken Puls, CMA

http://www.excelguru.ca

 

[Chrissirhc]

Thanks everyone for your help. I've implemented the code using the Windows API. I wanted to see if there were any other easier options. It ended up being a fair amount of code....

Cheers,

Chris

 

[Chrissirhc]

Having read through the authors getregistry code, there are some strange occurences. Would anyone be able to explain why? (Only those with the code would be able to understand the below.)

Two points:

1) He sets thekey, then he does a case statement then he has an if statement on thekey. Why not just use case else?
2) He does some logic using RegOpenKeyA and sets x if that returns true. Only to have another statement that sets x anyway... What is the point of that?

Thanks in advance,

Chris

 

[Chrissirhc]

Ok for (2) it looks like it creates the key and just needs something to hold the return value.

 

[Chrissirhc]

I've noticed that regopenkeya doesn't return an error code when the key doesn't exist. Isn't that strange?

Next thing I noticed is that regCreateKeyA doesn't exist in the DLL. There is something called regCreateKeyEx.

Any thoughts?