Referencing cell through constant problem

[dkj]

I am relatively new to Excel VBA programming and learning more and more as I go along.

I am trying to create a constant for a specific cell and later work with the value in that constant and I am struggling with the syntax. Here are two examples of what I am trying to do and not being successful:

Const cCustName As String = "(Sheet1.Cells(9, 4))"
Const cSiteNotes As Integer = 17 
Dim currentRow as Integer = 1
if isEmpty(cCustName) then
PromptForName
end if
if isEmpty(sheet3.cells(currentRow, cSiteNotes)
PromptForSite
end if

Thank you for looking at my problem

[Bob Phillips]

I am relatively new to Excel VBA programming and learning more and more as I go along.

I am trying to create a constant for a specific cell and later work with the value in that constant and I am struggling with the syntax. Here are two examples of what I am trying to do and not being successful:

Const cCustName As String = "(Sheet1.Cells(9, 4))"
Const cSiteNotes As Integer = 17 
Dim currentRow as Integer = 1
if isEmpty(cCustName) then
PromptForName
end if
if isEmpty(sheet3.cells(currentRow, cSiteNotes)
PromptForSite
end if

Thank you for looking at my problem

You can't set a constant to an object, and you cannot set a value in a Dim statement, you need to do it a different way.

Dim cCustName As String
Const cSiteNotes As Integer = 17
Dim currentRow As Integer
currentRow = 1
cCustName = Worksheets("Sheet1").Cells(9, 4).Value
If IsEmpty(cCustName) Then
        PromptForName
    End If
If IsEmpty(Worksheets("Sheet3").Cells(currentRow, cSiteNotes)) Then
        PromptForSite
    End If

[mdmackillop]

Hi DHJ,
Welcome to VBAX.
Adapting your code, try the following. If you want to post a sample of your workbook and code, you can do this by zipping the file and using the Manage Attachments button when you use the Go Advanced option. You can also format your code by selecting it and clicking on the VBA button.

Sub Test() 
Dim cCustName As Range
Dim CurrentRow As Integer
Const cSiteNotes As Integer = 17
Set cCustName = Sheets("Sheet1").Cells(9, 4)
CurrentRow = 1
If IsEmpty(cCustName) Then
    PromptForName
End If
If IsEmpty(Sheets("sheet3").Cells(CurrentRow, cSiteNotes)) Then
    PromptForSite
End If
End Sub
Sub PromptForName()
    MsgBox "give me a name"
End Sub
Sub PromptForSite()
    MsgBox "give me a site"
End Sub

[dkj]

Thank you for your two solutions. They make sense.