Dim problems

[kittronald]

What is the problem with the following ?

Dim r As Integer
Dim s As String

r = Application.Count(Sheet1.Range("Set_Size_Range"))
s = ActiveWorkbook.Names("Set_Size").RefersTo

If r >= s Then



When the IF line executes, a "Run-time error '13': Type mismatch" error
occurs.

The following are the debug.print outputs

r equals 126

s equals =ABS(MIN(Data_Header_Row))

If you type =Set_Size in a cell, the value returned is 126.

What is the syntax for assigning the value of the name "Set_Size" to the
variable s ?



kittronald

 

[Don Guillett]

    What is the problem with the following ?

        Dim r As Integer
        Dim s As String

        r = Application.Count(Sheet1.Range("Set_Size_Range"))
        s = ActiveWorkbook.Names("Set_Size").RefersTo

        If r >= s Then

    When the IF line executes, a "Run-time error '13': Type mismatch"error
occurs.

    The following are the debug.print outputs

        r equals  126

        s equals =ABS(MIN(Data_Header_Row))

    If you type =Set_Size in a cell, the value returned is 126.

    What is the syntax for assigning the value of the name "Set_Size"to the
variable s ?

kittronald

You may benefit from using
msgbox r
msgbox s
for testing

 

[kittronald]

Don,

Thanks for the quick reply.

I did that and got the same results as the debug.print.

I can see that variable r is returning a number and variable s is
returning the formula (string) in the Refers to: field of the name.

How can I get the variable s to evaluate to the value of the name
"Set_Size" which is the number 126.


kittronald

 

[Tim Williams]

s = ActiveWorkbook.Names("Set_Size").RefersTo
MsgBox Application.Evaluate(s)

Tim

 

[kittronald]

Tim,

Thanks for the response.

What I'm trying to do is get the variable s to equal the value that is
returned by the defined name "Set_Size".

The problem is the variable r is an integer and the Refers to: field of
the defined name "Set_Size" contains a formula (which makes it a string
value).

A type mismatch occurs because the IF statement is comparing two
different data types.

What's the best way to get the variable s to equal the integer value of
the "Set_Size" name ?


kittronald

 

[GS]

kittronald brought next idea :

Tim,

Thanks for the response.

What I'm trying to do is get the variable s to equal the value that is
returned by the defined name "Set_Size".

The problem is the variable r is an integer and the Refers to: field of
the defined name "Set_Size" contains a formula (which makes it a string
value).

A type mismatch occurs because the IF statement is comparing two
different data types.

What's the best way to get the variable s to equal the integer value of
the "Set_Size" name ?


kittronald

Use a Variant data type to retrieve the value stored in the defined
name, with the Evaluate() function.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[kittronald]

Garry,

Changed it to:

Dim s As Variant

s = Application.Evaluate(ActiveWorkbook.Names("Set_Size").RefersTo)


And that is why we call you THE MAN !

Thanks again !


kittronald

 

[GS]

Use a Variant data type to retrieve the value stored in the defined name,

with the Evaluate() function.

Here's an example...

Sub Setup_WksUI(Optional Wks As Worksheet)
Dim sz As String
Dim vSetting, vSettings
Dim i As Integer

If Wks Is Nothing Then Set Wks = ActiveSheet

vSettings = Split(msUI_SETTINGS, ",")

'The sheet must be visible and not protected to do this
Wks.Unprotect PWRD
Wks.Visible = xlSheetVisible

For i = LBound(vSettings) To UBound(vSettings)
'Determine if the current sheet requires the current setting
vSetting = Empty
On Error Resume Next
If vSettings(i) = "uiScrollArea" Then
Set vSetting = Application.Evaluate("'" & Wks.name & "'!" &
vSettings(i))
Else
vSetting = Wks.Names(vSettings(i)).RefersTo
If Not (vSetting = Empty) Then vSetting =
Application.Evaluate("'" & Wks.name & "'!" & vSettings(i))
End If 'rngCell.Value = "uiScrollArea"
On Error GoTo 0

If Not IsEmpty(vSetting) Then
Select Case vSettings(i)
Case "uiProgRows": If vSetting > 0 Then
Wks.Range("A1").Resize(vSetting).EntireRow.Hidden = True
Case "uiProgCols": If vSetting > 0 Then
Wks.Range("A1").Resize(, vSetting).EntireColumn.Hidden = True
Case "uiScrollArea": Wks.ScrollArea = vSetting.address
Case "uiSelect": Wks.EnableSelection = vSetting
Case "uiFilter": Wks.EnableAutoFilter = vSetting
Case "uiRowColHdrs": Wks.Activate:
Application.ActiveWindow.DisplayHeadings = vSetting
Case "uiProtect": If vSetting Then wksProtect Wks.name
Case "uiVisible": Wks.Visible = vSetting
Case "uiOutline": Wks.EnableOutlining = vSetting

'Persist any changes the user makes during runtime
Case "uiOutlineR"
If Application.Evaluate("'" & Wks.name & "'!" & "uiSet") = 0
Then Wks.Outline.ShowLevels RowLevels:=vSetting:
Wks.Names("uiSet").RefersTo = "=1"
Case "uiOutlineC"
If Application.Evaluate("'" & Wks.name & "'!" & "uiSet") = 0
Then Wks.Outline.ShowLevels ColumnLevels:=vSetting:
Wks.Names("uiSet").RefersTo = "=1"
End Select 'Case vSettings(i)
End If 'Not IsEmpty(vSetting)
Next

End Sub 'Setup_WksUI()

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion