MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

making a spreadsheet cell name a global variable in the VBA project

  Asked By: Dale    Date: Jan 26    Category: MS Office    Views: 4552

Is it possible to make a spreadsheet cell (preferable by 'Cell name'),
a global variable in a VBAProject? Or this is really not proper
programming technique..

But there are instances where it will be very handy (otherwise one
needs to add it to all function calls).



3 Answers Found

Answer #1    Answered By: Adalgisa Miller     Answered On: Jan 26


Define the global  variable as a range. Then set it to the reference during


Public MyCell As Range

Set MyCell = Range("MyRangeName")

Answer #2    Answered By: Girja Garg     Answered On: Jan 26

Just an additional question, If I want to test if the cell  name
'MyRangeName' exists in the worksheet, how do I do that?

So what I want to do, First test if the cellname exist an dif it exist
then read the cell name.

Answer #3    Answered By: Tyler Thompson     Answered On: Jan 26

You can look through the collection of range names, or you can cheat. :-)

If you put

On Error Resume Next

just before your Set statement, and

On Error Resume 0

just after the Set statement to turn error checking back on, the Set statement
will do nothing if the range doesn't exist. You'll then be able to test it,
probably with

If MyCell Is Nothing Then ...

(but that might not be exactly the right If).

If you want to do it properly, the following function  is from my library

Public Function NamedItemExists(ByVal ItemName As String) As Boolean
Dim Item As Variant
On Error GoTo DoesntExist
Item = Application.Range(ItemName)
NamedItemExists = True
Exit Function
NamedItemExists = False
End Function

Oops - I cheated too. :-( But at least I hid it in a
function. :-)


If Not NamedItemExists ("MyRangeName") Then
MsgBox ("Houston, we have a problem")
End If

However, you can use the Application.Names collection if you want to know
explicitly what range names there are.