Link to home
Create AccountLog in
Avatar of Cook09
Cook09Flag for United States of America

asked on

How to use the value of a Name in VBA

If one has defined a name, that references a particular cell within a specific sheet, with a Workbook scope, how does one reference the value of that defined name within VBA.

For example:

nSht2LastCol  = Database!$A$17  or
nSht3NextRow = Database!$A$28

yet when used within VBA, the nSht3NextRow will be null unless a statement is written (similar to that in Name Manager)  that references the name to the cell.  

This would seem to defeat the whole purpose of having defined names.  It had been suggested that using a shortcut bracket [nSht3NextRow] would produce the value of the Defined Name, but that does not seem to be the case unless it is referenced first to the Sheet!Cell.

Does anyone know if it is possible to reference a defined name within VBA without first writing a statement that ties the two together as it is in Name Manager?

Thanks,

Cook
Avatar of spattewar
spattewar

have you defined the name in the Name Manager? Once you have defined that you can use either of the statements below to get the value of the range in VBA

Application.Range("nSht3NextRow")

OR

[nSht3NextRow]

But you have to define the name in the Name Manager first.
Avatar of Cook09

ASKER

Is this what you mean by defining it in Name Manager?  See Image.

If so, that's the basis of my question, the value does not transfer over.  However, is there a difference in the value transferring if the Code is in Break Mode (debugging) or not?


NameManager.jpg
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Cook09

ASKER

What happened with using:

[nSht3NextRow]

I thought that was supposed to provide the value in shortcut mode.
what do you get if you do

Msgbox [nSht3NextRow]

In the name manager when you select this name and click on the refers to box does excel take you to the cell that it refers to and highlights it?
Avatar of Cook09

ASKER

The Message Box shows the correct value.
so you are getting the correct value when you reference the range in VBA code. Are you setting the range reference also in the code before you are displaying the value? If yes then you can remove that and check the message box value.
Avatar of Cook09

ASKER

I'm not sure I fully understand what you may be referring too.  At first, I added the names through VBA (see code).  However, I started getting errors in this initialization Sub, so I commented most of those out.  While the testing hasn't been exhaustive, and maybe redundant, adding the following code to the initialization Sub seemed to work, but I haven't removed all of the other references where I perform the same function within each Sub.  

Obviously, having one area where one's variables are located is better (in some cases) than setting them up in each Module.  Not having to initialize them at all is even better.

It's been a work in progress, because while the Name Manager would show the correct value, that value would not be passed to another variable, or used correctly within the Sub.  Even trying to print the variable within the Immediate Window would produce a null value. Specifically referencing the name to the cell, allowed that name to capture the value within the assigned cell.  

Which is confusing, as [Defined Names] should be available within their scope. That was one of the reasons for asking if there is a difference in Defined Names during the debugging process while in Break mode, or should it work anyway (which one would think it should).


With ActiveWorkbook.Names
.Add Name:="nSht3LastCol", RefersTo:=Worksheets("Database").Range("A27")
End With

Sub InitialzeProjVar()
Dim WSD As Worksheet
Set WSD = Worksheets("Database")

             nSht3LastCol = WSD.Range("A27")
             nSht3NextRow = WSD.Range("A28")
End Sub

Open in new window

can you do one thing?

Comment all the name initialization from the VBA code. This will make sure that the name are all defined in the name manager and not in the code.

Does your workbook (where all the names are defined) contain the VBA code? If yes then you should be able to access the values of the names by the following code

Application.Range("nSht3LastCol")

OR

ThisWorkbook.Sheets("Database").Range("nSht3LastCol")

Also make sure that you have not defined any variable in your VBA code that have the same name as the range names defined in the name manager.

It would be helpful if you can post the workbook so that I can have a look.

Thanks.

Avatar of Cook09

ASKER

The Workbook itself is very involved with some proprietary information inside.  As I've run into a couple of other difficulties, I will attempt what you have suggested by the end of the day.  If I'm still having the same issues I'll see about making a "Lite" version that would demonstrate what is occurring.
Sounds like you might be having scoping issues.  Is there a need to have names defined within each worksheet?  You can have the same name defined in different worksheets that reference different things.

If so, then fine.  If not, then define your names where the scope is the Workbook, not the sheet.

Also, in your VBA programming, you could have globally scoped variables - defined in only one module :  Public dim i as integer - is an example

Avatar of Cook09

ASKER

As a quick follow-up, while I'm taking care of other issues, there was the opportunity to do as you suggested and it was discovered  that a couple of Names referenced the same cell.  Whether that is the solution to the issue is still to be seen as I haven't  been to the one's in question.  It seems that adding Defined Names manually and then through VBA represents a potential for duplicates occurring.

One area that is a little puzzling is the "Refers to:" when adding a Name.  Outside of a specific cell or range what is allowed to be referenced there?  Does it have to be a specific range, or can it be used like a Public Variable? If so, what is then placed in the Refers to:?

Secondly, if one adds a Defined Name in VBA, is that routine run only once and then set aside?  Once the Name it's added, it's added.  So, does that then become similar to a Setup Sub, which is run at the beginning, and then a second Sub takes over and initializes the values each time the project is run to reset the previous values?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Cook09

ASKER

In using the comments that have been provided, it seems that there were some conflicts with the Names, and in not using the names correctly.  However, I do have one more question with Names that I haven't found the right syntax for:

If I have a name that references a formula, which happens to be the heart and soul of the project,  and it needs to be refreshed each time the workbook is opened, with the exception of the .Add Names, I haven't found a way to refresh that Defined Name. The code works now, even in the .Add state, but how would I refresh nUserID without using the .Add method?
Sub Initialize()
    With ActiveWorkbook.Names
        .Add Name:="nUserID", RefersTo:=Environ("UserName")
     End With
End Sub

Open in new window

i think you can do this to refresh the range

With ActiveWorkbook.Names("nUserID")
        .RefersTo=.RefersTo
End with

Avatar of Cook09

ASKER

That method I haven't tried, however if there are two or three of these, do I use:

With ActiveWorkbook
   .Names("Name").RefersTo=Formula
  ...

End With
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Cook09

ASKER

I guess this is why EE is so valuable.  What you provided I have not been able to find in any of my reference material, and spent two or three hours yesterday trying to figure out how to accomplish this, before "throwing in the towel."  Through this threaded discussion, the use of Names is now a lot more clearer.

One last thought.  Public Names are supposed to be available during the project, but I've experienced times that if an error occurs, or if I'm testing a new Userform or access method, the value of the Public Name may become Null.  Defined Names seem to hold their value more consistently.  Yet all of the research books seem to focus on Public Names (and to be used rarely), instead of Defined Names.

Is there a downside to using Defined Names to hold values that will be referenced in several modules?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Cook09

ASKER

Dave,

Thanks for the exhaustive response, your little utility was useful in that I found names that were linked to other workbooks and some that had errors associated with them.  I thought I had looked at the Name Manager for issues, but it could have been during one of my recent "all-nighters" and just got missed. The utility made it very easy to spot potential problems.

I'm going to perform a simple test on the RefersTo: issue.  Since I'm connected to a Network, the formula:

Environ("UserName")  should reassert my login, regardless of changing it.  Which is what I'm really after, within the code initialization Sub.  We'll see if it changes or not.

Some of the references  I'll also need to read, but within a little bit should be able to see how they are complimentary to the project.

Thanks again for the detailed response, I appreciate the discussion and utility tips.

Cheers,

Ron
my code was to refresh the range values. Ideally there should be no need to refresh it as when when you use it you will get the latest value.
Avatar of Cook09

ASKER

After some checking and using the defined Names, most of it is now making sense.  The RefersTo: would just initialize the name if needed, or to set the names to their original value, through:

  With ActiveWorkbook
                 .Names("nUserID").RefersTo = Environ("UserName")
  End With

I have run into one  issue though:

This deals with Arrays.
The name is set as nstrHis = HisVerify()  and HisVerify defined as HisVerify(1 To 3) As String
If I set HisVerify(1) = "Ron"
A debug.print HisVerify(1) works fine and displays Ron
What is the syntax to print it from the Defined Name?
Name Manager  shows {"Ron","",""}

I've tried:
debug.print [nstrHis(1)]  and [nstrHis](1)  but both error out (2023).  I know there must be a way, it's not as straightforward as the variable itself.

Thanks for taking time with this,
Ron

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Cook09

ASKER

Yes I can do that, it just seemed that if the Name Manager displayed:

nMyName ={"Ron","",""}

that there should be an easy way to extract the "Ron" from the Name.  Maybe I'll do some experimenting.

Thanks for all your help on this...

Cheers,

Ron
Avatar of Cook09

ASKER

Very through and comprehensive.  Excellent.  Thanks guys....

Ron