PDA

View Full Version : [SOLVED] Referencing cell through constant problem



dkj
07-07-2005, 11:02 AM
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
07-07-2005, 11:13 AM
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
07-07-2005, 11:17 AM
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
07-07-2005, 11:23 AM
Thank you for your two solutions. They make sense.