Private Const myValue = "the answer is 42"

Public Function getSecret()
  getSecret = myValue
End Function

Sub test()
    Dim sheetName = "Sheet1"
    MsgBox "The sheet says that " & Sheets(sheetName).getSecret()
End Sub