Hi all,
I need to store persistent configuration settings of a custom Excel 2003
add-in.
Which is best way to do this in your opinion?
I can suppose these possible solutions:
1) write configuration settings in a text file and load it every time is
needed
2) write settings in a "hidden" cell or worksheet (but I don't like this);
is there another way to store this information inside the .xls file?

Thanks in advance,

faffo1980

1) In a hidden worksheet that is protect. Make the cells readable but not
writable
2) Store text in a module as comments. Again you can make th emodule
invisable. the text can be read from VBA code.

"faffo1980" wrote:

Hi all,
I need to store persistent configuration settings of a custom Excel 2003
add-in.
Which is best way to do this in your opinion?
I can suppose these possible solutions:
1) write configuration settings in a text file and load it every time is
needed
2) write settings in a "hidden" cell or worksheet (but I don't like this);
is there another way to store this information inside the .xls file?

Thanks in advance,

faffo1980

Hi Joel,
thanks a lot for your reply.
What do you mean with "Store text in a module AS A COMMENT"?
Sorry for the question but I'm very new wih VBA..How can I read/modify
comment withiin a module?

Thanks again

Regards

"Joel" wrote:

1) In a hidden worksheet that is protect. Make the cells readable but not
writable
2) Store text in a module as comments. Again you can make th emodule
invisable. the text can be read from VBA code.

"faffo1980" wrote:

Hi all,
I need to store persistent configuration settings of a custom Excel 2003
add-in.
Which is best way to do this in your opinion?
I can suppose these possible solutions:
1) write configuration settings in a text file and load it every time is
needed
2) write settings in a "hidden" cell or worksheet (but I don't like this);
is there another way to store this information inside the .xls file?

Thanks in advance,

faffo1980

I assumed you were storing text strings. With text you can always remove the
single quote comments.

Cpearson in the website below gives lots of examples for reading/writing to
modules

http://www.cpearson.com/Excel/vbe.aspx


so you can simply put comments into the modules

'line1
'line2
'line3


Then when you read the lines remove the comments

inputline = mid(MyString,2) this will skip the 1st character when reading
the line.


"faffo1980" wrote:

Hi Joel,
thanks a lot for your reply.
What do you mean with "Store text in a module AS A COMMENT"?
Sorry for the question but I'm very new wih VBA..How can I read/modify
comment withiin a module?

Thanks again

Regards

"Joel" wrote:

1) In a hidden worksheet that is protect. Make the cells readable but not
writable
2) Store text in a module as comments. Again you can make th emodule
invisable. the text can be read from VBA code.

"faffo1980" wrote:

Hi all,
I need to store persistent configuration settings of a custom Excel 2003
add-in.
Which is best way to do this in your opinion?
I can suppose these possible solutions:
1) write configuration settings in a text file and load it every time is
needed
2) write settings in a "hidden" cell or worksheet (but I don't like this);
is there another way to store this information inside the .xls file?

Thanks in advance,

faffo1980

Hi faffo198,

The best way to do this is to use the registry functions offered by Excel. I know, writing stuff to the registry sounds scary, but it's safe if you use Excel's built-in functions. The three functions that you will be using are SaveSetting, GetSetting and DeleteSetting.

SaveSetting allows you to store values in the registry, GetSetting allows you to retrieve those values and you use DeleteSetting to clean up once you are done. These three functions require an application name, a section name and a key name (optional for DeleteSetting). My suggestion is to set the first two using global constants:
Global Const sAppName = "MyAddin"
Global Const sSectionName = "Config"

Then define three functions:
'Retrieve the value of the "key" registry key under sAppName\sSectionName
Public Function get_value(key As String) As String

get_value = GetSetting(sAppName, sSectionName, key)
End Function

'Set the value of the "key" registry key under sAppName\sSectionName
Public Sub set_value(key As String, key_value As String)

SaveSetting sAppName, sSectionName, key, key_value
End Sub

'Delete the sAppName\sSectionName registry path (will delete all keys in that path too)
Sub clear_registry()

On Error Resume Next
DeleteSetting sAppName, sSectionName
On Error GoTo 0
End Sub

And you can now save and restore configuration settings in a persistent manner. Example:

Sub LastUsed()
dim last_used as string

'Retrieve last used date
On Error Resume Next
last_user = get_value("LastUsed")
On Error GoTo 0

MsgBox "This add-in was last used on " & last_used

'Set new last used date
set_value "LastUsed", CStr(Now)
End Sub


Sub CleanUp()

'Erase all registry keys stored in sAppName\sSectionName
'Don't use if you wish for the data to persist after you close Excel
clear_registry
End Sub

Cheers,

Benoit

On Monday, August 10, 2009 6:00 AM faffo198 wrote:

Hi all,
I need to store persistent configuration settings of a custom Excel 2003
add-in.
Which is best way to do this in your opinion?
I can suppose these possible solutions:
1) write configuration settings in a text file and load it every time is
needed
2) write settings in a "hidden" cell or worksheet (but I do not like this);
is there another way to store this information inside the .xls file?

Thanks in advance,

faffo1980


On Monday, August 10, 2009 6:07 AM Joe wrote:

1) In a hidden worksheet that is protect. Make the cells readable but not
writable
2) Store text in a module as comments. Again you can make th emodule
invisable. the text can be read from VBA code.

"faffo1980" wrote:


On Monday, August 10, 2009 6:48 AM faffo198 wrote:

Hi Joel,
thanks a lot for your reply.
What do you mean with "Store text in a module AS A COMMENT"?
Sorry for the question but I am very new wih VBA..How can I read/modify
comment withiin a module?

Thanks again

Regards

"Joel" wrote:


On Monday, August 10, 2009 6:57 AM Joe wrote:

I assumed you were storing text strings. With text you can always remove the
single quote comments.

Cpearson in the website below gives lots of examples for reading/writing to
modules

http://www.cpearson.com/Excel/vbe.aspx


so you can simply put comments into the modules

'line1
'line2
'line3


Then when you read the lines remove the comments

inputline = mid(MyString,2) this will skip the 1st character when reading
the line.


"faffo1980" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: Excel 2010 - The Missing Manual [OReilly]
http://www.eggheadcafe.com/tutorials...l-oreilly.aspx