PDA

View Full Version : [SOLVED] Problems with GetSetting function



Vundra
02-07-2017, 10:36 AM
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
02-07-2017, 11:40 AM
Maybe try something like




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


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

SamT
02-07-2017, 02:12 PM
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
02-08-2017, 10:23 AM
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
02-09-2017, 10:09 AM
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
02-09-2017, 10:52 AM
I think the code I posted in #2 does all that a lot simpler

Vundra
02-10-2017, 09:50 AM
Paul, I've tried your code posted in #2 but as I said it doesn't work.

Paul_Hossler
02-10-2017, 11:05 AM
This is a more wordy and more complete equivalent and it works on my computer

18320


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
02-10-2017, 03:43 PM
@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
02-15-2017, 10:14 AM
The code in post #8 works really fine. Many thanks for the help Paul