EVALUATE Method and arguments

L

lumpus

the following code confused me. Can someone explain to me what I am setting
VSetting to.
vSetting = Application.Evaluate( _
"'" & wksSheet.Name & "'!" & _
rngName.Value)

Also what does "'"& wksSheet.Name&"!" refer to
what does setting Vsetting = Empty do?

Finally, could you give me an explanation of the evaluate method with an
example for context.

Entire code below for context. Thanks for the help

Public Sub ReadSettings()

Dim lOffset As Long
Dim rngName As Range
Dim rngNameList As Range
Dim rngSetting As Range
Dim sMsg As String
Dim vSetting As Variant
Dim uAnswer As VbMsgBoxResult
Dim wkbBook As Workbook
Dim wksSheet As Worksheet

' This process is irreversible. Warn the user before
' clearing the existing contents of the table.
uAnswer = vbNo
sMsg = "Do you want to overwrite the table with" _
& vbLf & "the current template settings?"
uAnswer = MsgBox(sMsg, vbQuestion + vbYesNo)

If uAnswer = vbYes Then

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wkbBook = Application.Workbooks(msFILE_TIME_ENTRY)

wksUISettings.UsedRange.Offset(1, 0).Clear

wkbBook.Activate
Set rngNameList = wksUISettings.Range(msRNG_NAME_LIST)

For Each wksSheet In wkbBook.Worksheets

lOffset = lOffset + 1

With wksUISettings.Range("A1").Offset(lOffset, 0)

.Value = wksSheet.CodeName
'MsgBox wksUISettings.Range("A1").Offset(lOffset, 0).Value =
wksSheet.CodeName

For Each rngName In rngNameList

Set rngSetting = Intersect(.EntireRow, _
rngName.EntireColumn)

' The setScrollArea setting requires special
' treatment because it's a named range as
' opposed to a named constant.
If rngName.Value = "setScrollArea" Then

' This setting may not exist,
' therefore we wrap it in
' On Error Resume Next.
On Error Resume Next
rngSetting.Value = _
wksSheet.Range("setScrollArea").Address
On Error GoTo 0

Else

vSetting = Empty
vSetting = Application.Evaluate( _
"'" & wksSheet.Name & "'!" & _
rngName.Value)

If Not IsError(vSetting) Then
rngSetting.Value = vSetting
End If

End If

Next rngName

End With

Next wksSheet

ThisWorkbook.Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End If

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top