Passing Variables from VBA instance to Non-Vba Application

[white605]

While working through various ways to pass a variable such as
a "username","password", or entire "entire connection string"
from word or excel to another application after first exiting the original program I have worked thru the following methods.

Hard Way - create a new registry key and rite the info there so as to hide it somewhat - then read it into the next application, use it, and erase it.

Easy Way - rite the info to a text file, use it, and delete it. Values left for anyone to see for a time eaisly

Basic Question - Is there a way to declare and access what i would call "Global or Operating System" variables that will live after exiting the vba application and die either at reboot or at some other appropriate time?

I would prefer to use the KISS method if possible.

Sample of functions I modified to manipulate Keys

Sub TestRegistry()
Dim myRegKey As String
Dim myValue As String
Dim myAnswer As Integer

  'get registry key to work with
'UNCOMMENT NEXT LINE TO RUN
  'myRegKey = InputBox("Which registry key do you want to read?", _
             "Get Registry Key")
  If myRegKey = "" Then Exit Sub
  'check if key exists
  If RegKeyExists(myRegKey) = True Then
    'key exists, read it
    myValue = RegKeyRead(myRegKey)
    'display result and ask if it should be changed
    myAnswer = MsgBox("The registry value for the key """ & _
               myRegKey & """" & vbCr & "is """ & myValue & _
               """" & vbCr & vbCr & _
               "Do you want to change it?", vbYesNo)
  Else
    'key doesn't exist, ask if it should be created
    myAnswer = MsgBox("The registry key """ & myRegKey & _
               """ could not be found." & vbCr & vbCr & _
               "Do you want to create it?", vbYesNo)
  End If
  If myAnswer = vbYes Then
    'ask for new registry key value
    myValue = InputBox("Please enter new value:", _
              myRegKey, myValue)
    If myValue <> "" Then
      'save/create registry key with new value
      RegKeySave myRegKey, myValue
      MsgBox "Registry key saved."
    End If
  End If
  
  'ask if key should be deleted from registry
  myAnswer = MsgBox("Do you want to delete the registry key """ & _
             myRegKey & """?", vbYesNo)
  If myAnswer = vbYes Then
    'delete registry key
    If RegKeyDelete(myRegKey) = True Then
      'deletion was successful
      MsgBox "Registry key """ & myRegKey & """ deleted."
    Else
      'deletion wasn't successful
      MsgBox "Registry key """ & myRegKey & _
             """ could not be deleted."
    End If
  End If
  MsgBox "myvalue"
End Sub

'returns True if the registry key i_RegKey was found
'and False if not
Function RegKeyExists(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'try to read the registry key
  myWS.RegRead i_RegKey
  'key was found
  RegKeyExists = True
  Exit Function
  
ErrorHandler:
  'key was not found
  RegKeyExists = False
End Function

'sets the registry key i_RegKey to the
'value i_Value with type i_Type
'if i_Type is omitted, the value will be saved as string
'if i_RegKey wasn't found, a new registry key will be created
Sub RegKeySave(i_RegKey As String, _
               i_Value As String, _
      Optional i_Type As String = "REG_SZ")
Dim myWS As Object

  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'write registry key
  myWS.RegWrite i_RegKey, i_Value, i_Type

End Sub

'reads the value for the registry key i_RegKey
'if the key cannot be found, the return value is ""
Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function

'deletes i_RegKey from the registry
'returns True if the deletion was successful,
'and False if not (the key couldn't be found)
Function RegKeyDelete(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'delete registry key
  myWS.RegDelete i_RegKey
  'deletion was successful
  RegKeyDelete = True
  Exit Function

ErrorHandler:
  'deletion wasn't successful
  RegKeyDelete = False
End Function

Thanks for your time and expertise
wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[TCARPENTER]

Hi white605,

I vote for the text file. Using the registry is fine, I just don't poke around in them much so they don't bloat. If you need to hide something, you can always read and write the file as binary, or look into creating a VB database, (there's a whole forum dedicated to this), but one of my most used ways is to just create an Access database, and use that. Finally, if you're into ADO, check out persisting a recordset to file, you may like that option as well.

HTH
Todd

 

[combo]

I would rather avoid messages with 'enter registry key' or similar text, instead: 'enter password' or 'enter user name'.
As for registry - VB(A) has GetSetting, GetAllSettings and SaveSetting native functions that allow fast and secure access to 'VB and VBA Settings' section of the registry.



combo