Problems with GetSetting function

[Vundra]

I've written these two simple macros:

Sub SaveValues() 
Dim cmtFontStrikethrough As Boolean 
Dim cmtFontSuperscript As Boolean 
Dim cmtFontSubscript As Boolean
Dim cmtFontUnderline As Variant 
 
Range("A1").AddComment 
Range("A1").Comment.Text Text:="ABC" 
Range("A1").Comment.Visible = True 
Range("A1").Comment.Shape.Select True 
With Selection 
    cmtFontName = .Characters.Font.Name 
    cmtFontStyle = .Characters.Font.FontStyle 
    cmtFontSize = .Characters.Font.Size 
    cmtFontUnderline = .Characters.Font.Underline 
    cmtFontStrikethrough = .Characters.Font.Strikethrough 
    cmtFontSuperscript = .Characters.Font.Superscript 
    cmtFontSubscript = .Characters.Font.Subscript 
     
    SaveSetting "Test", "Font", "1", cmtFontName 
    SaveSetting "Test", "Font", "2", cmtFontStyle 
    SaveSetting "Test", "Font", "3", cmtFontSize 
    SaveSetting "Test", "Font", "4", cmtFontStrikethrough 
    SaveSetting "Test", "Font", "5", cmtFontSuperscript 
    SaveSetting "Test", "Font", "6", cmtFontSubscript 
    SaveSetting "Test", "Font", "7", cmtFontUnderline 
End With 
End Sub

Sub GetValues() 
Range("A7").AddComment 
Range("A7").Comment.Text Text:="ABC" 
Range("A7").Comment.Visible = True 
Range("A7").Comment.Shape.Select True 
With Range("A7").Comment.Shape.TextFrame.Characters.Font 
 
    .Name = GetSetting("Test", "Font", "1") 
    .FontStyle = GetSetting("Test", "Font", "2") 
    .Size = GetSetting("Test", "Font", "3") 
'    .Strikethrough = GetSetting("Test", "Font", "4") 'Don't work! 
'    .Superscript = GetSetting("Test", "Font", "5") 'Don't work! 
'    .Subscript = GetSetting("Test", "Font", "6") 'Don't work!     
'    .Underline = GetSetting("Test", "Font", "7") 'Don't work! 
End With 
End Sub

I've noticed these facts into the registry:
1) for Strikethrough, Superscript, Subscript I find "Falso" (italian) and not "False"; if I convert manually these registry values in "False" the routine works fine
2) for Underline I find "-4142" (no underline) ; the problem is the inverted commas (that I cannot remove) because if change the code line in .Underline =-4142 the routine works

There is a way to solve these issues in Excel 2007?

[Paul_Hossler]

Maybe try something like

SaveSetting "Test", "Font", "4", IIF (.Characters.Font.Strikethrough, "Y", "N")


.Strikethrough = (GetSetting("Test", "Font", "4")  = "Y"))

[SamT]

Try using
-1 (minus one) for True and Yes
0 (zero) for False and No

I would

Const Yay as Variant = -1 'True
Const Non Variant = 0 'False
'
'
'
.Characters.Font.Underline = Yay
.Characters.Font.Bold = Non
'
'
'

[Paul_Hossler]

GetSetting only returns a String, but sometimes Excel can coerce it into another Type depending the registry value and the type of variable you're assigning it to

Option Explicit
Sub Settings()
    Dim v As Variant
    Dim b As Boolean
    
    'vbString 8 String
    'vbBoolean 11 Boolean value
    
    SaveSetting "AppName", "Section1", "Key1", True
    v = GetSetting("AppName", "Section1", "Key1")
    MsgBox v        '   returns String
    MsgBox VarType(v)
 
    b = GetSetting("AppName", "Section1", "Key1")
    MsgBox b    '   returns string, but Excel coerces it into a boolean since "True" can = True
    MsgBox VarType(b)
 
 
    SaveSetting "AppName", "Section1", "Key1", -1
    v = GetSetting("AppName", "Section1", "Key1")
    MsgBox v    '   returns string
    MsgBox VarType(v)
 
    b = GetSetting("AppName", "Section1", "Key1")
    MsgBox b           '   returns string, but Excel can coerce it into a boolean since "-1" can = True
    MsgBox VarType(b)
 
 
    SaveSetting "AppName", "Section1", "Key1", "SomeData"
    v = GetSetting("AppName", "Section1", "Key1")
    MsgBox v            '   really is a string
    MsgBox VarType(v)
 
    b = GetSetting("AppName", "Section1", "Key1")   '   Err Type mismatch 13 since 'SomeData" cannnot be coerced into a Boolean
    MsgBox b
    MsgBox VarType(b)
 
End Sub

So I always use String values to make it easier and less chance for errors

[Vundra]

Paul & Sam
First of all thanks,mates

Well, for boolean variables Excel wants only "True" or "False" , not letters or numbers (-1,0) ... I get errors using them.
I'm able to write "True" or "False" into the registry with these two lines:

cmtFontStrikethrough = IIf(.Characters.Font.Strikethrough, "True", "Vero")
cmtFontStrikethrough = IIf(.Characters.Font.Strikethrough, "False", "Falso")

so I need to combine (and I don't know how) these two possible conditions when I write:

SaveSetting "Test", "Font", "4", cmtFontStrikethrough

With the right boolean values into the registry I don't need to modify the code in the GetSetting function.
I've noticed that controls (like check boxes) don't have this issue because in the registry I find "True" or "False" for them.

The thing is different for:

cmtFontUnderline = .Characters.Font.Underline (possible values -4142 none, 2 single, -4119 double)

because I need to remove the quotation marks ("-4142", "2", "-4119") in

.Underline = GetSetting("Test", "Font", "7")

[Paul_Hossler]

I think the code I posted in #2 does all that a lot simpler

[Vundra]

Paul, I've tried your code posted in #2 but as I said it doesn't work.

[Paul_Hossler]

This is a more wordy and more complete equivalent and it works on my computer

Capture.JPG


I added four settings to A1 to make sure that A7 would use them

        .Characters.Font.Name = "Arial"
        .Characters.Font.FontStyle = "Bold"
        .Characters.Font.Size = "13"
        .Characters.Font.Underline = True

Option Explicit
 
Sub SaveValues()
    Dim cmtFontName As String
    Dim cmtFontStyle As String
    Dim cmtFontSize As String
    Dim cmtFontStrikethrough As Boolean
    Dim cmtFontSuperscript As Boolean
    Dim cmtFontSubscript As Boolean
    Dim cmtFontUnderline As Variant
 
    Range("A1").AddComment
    Range("A1").Comment.Text Text:="ABC"
    Range("A1").Comment.Visible = True
    Range("A1").Comment.Shape.Select True
    With Selection
        
        .Characters.Font.Name = "Arial"
        .Characters.Font.FontStyle = "Bold"
        .Characters.Font.Size = "13"
        .Characters.Font.Underline = True
        
        cmtFontName = .Characters.Font.Name
        cmtFontStyle = .Characters.Font.FontStyle
        cmtFontSize = .Characters.Font.Size
        cmtFontUnderline = .Characters.Font.Underline
        cmtFontStrikethrough = .Characters.Font.Strikethrough
        cmtFontSuperscript = .Characters.Font.Superscript
        cmtFontSubscript = .Characters.Font.Subscript
         
        SaveSetting "Test", "Font", "1", cmtFontName
        SaveSetting "Test", "Font", "2", cmtFontStyle
        SaveSetting "Test", "Font", "3", cmtFontSize
        SaveSetting "Test", "Font", "4", cmtFontStrikethrough
        SaveSetting "Test", "Font", "5", cmtFontSuperscript
        SaveSetting "Test", "Font", "6", cmtFontSubscript
        SaveSetting "Test", "Font", "7", cmtFontUnderline
    End With
End Sub

Sub GetValues()
    Range("A7").AddComment
    Range("A7").Comment.Text Text:="ABC"
    Range("A7").Comment.Visible = True
    Range("A7").Comment.Shape.Select True
    With Range("A7").Comment.Shape.TextFrame.Characters.Font
        .Name = GetSetting("Test", "Font", "1")
        .FontStyle = GetSetting("Test", "Font", "2")
        .Size = GetSetting("Test", "Font", "3")
        .Strikethrough = CBool(GetSetting("Test", "Font", "4"))
        .Superscript = CBool(GetSetting("Test", "Font", "5"))
        .Subscript = CBool(GetSetting("Test", "Font", "6"))
        .Underline = CLng(GetSetting("Test", "Font", "7"))
    End With
End Sub

[Aussiebear]

@Vundra ("Kundra") didn't you get enough help at Mr Excel.com? BTW both forums politely ask to indicate any cross posting.... something you clearly have failed to do.

[Vundra]

The code in post #8 works really fine. Many thanks for the help Paul