Excel/Excel 2003 VBA

Advertisement


Question
QUESTION: Dear Andrea,

I have a problem with how to use "Name" correctly in Excel 2003 Visual Basic when referring to a named cell. Below is code that will exhibit what I'm trying to do.

Statement Lines 1 & 2 - I open my test data file and select Cell A1.

In Line 3 I name the selected cell "StartRecord"

In Line 4, to validate that I have done things correctly,
MsgBox ActiveCell displays the contents of the Activecell (Cell A1) correctly.

But in Line 5, when I use MsgBox StartRecord, the MsgBox output displays blank.

Why doesn't Line 5 display the contents of Cell A1?

Please help. What am I doing wrong? How can I display the contents of a named cell?

Bob Kunz


Sub New()

Line 1 - Workbooks.OpenText Filename:="C:\MyTest\TestData.TXT"     

Line 2 - Range("A1").Select          

Line 3 - ActiveCell.Name = "StartRecord"  
       
Line 4 - MsgBox ActiveCell

Line 5 - MsgBox StartRecord

ANSWER: Bob -

The first problem I come against is Line 1 - Why are you opening a text document?

Past that: if I ran the remainder of the code using the spreadsheet I had open, the message box returned correct. If I ran the code using the Line 1 to open an Excel workbook, it stopped at line 2, I believe because it needs more indication that it is looking for cell A1 in the recently opened sheet; however, I didn't want to go farther until I found out what the target of Line 1 is. That will make all the difference in the code.

Please let me know - where are you getting cell A1 from? - Andrea

---------- FOLLOW-UP ----------

QUESTION: Dear Andrea,

I have an Excel program which processes financial information. Some of the input data to my program is provided by my broker as a tab delimited format file. I take what I can get.

I really don't understand what you wrote about cell A1 needing a "target". When the .txt file is opened in my Line 1, cell A1 is automatically identified as the selected cell. However, just to be sure, I included Line 2. That makes cell A1 the selected file AND the Activecell which is named in Line 3.

Line 4 and 5 work as described in my original question.

Should I open the .txt file differently or do something else before naming cell A1? Please advise.

Thank you.

Bob Kunz

Answer
Bob-

Right now your code asks to open the .txt file in Excel; but the remaining commands are not pointed at that newly opened file, they default to the same file that contains the VBA code - which is why you are returning what you are. That's why I asked you which A1 you were trying to look at, I wanted to know which spreadsheet you are trying to access with the VBA code.

You will need to refer to the .txt data spreadsheet that gets created for all of your commands, otherwise your code will be executing commands in the wrong spreadsheet. This will require Dim-ing a spreadsheet and range:

   Dim wbk As Workbook
   Dim rng As Range
   
   Workbooks.OpenText Filename:="C:TestData.txt"

   Set wbk = Workbooks.Open("C:TestData.xls")

At this point you will be "acquiring" the wbk and using it for the rest of the code...and that's as far as I can get you, because there seems to be enough differences between 2003 and 2010 that I am not able to run the code to test it. I am sorry. Try another Expert who has 2003 on a machine, or do a search on this issue and you will probably be able to get the little bit you need to finish the job.

Hope this at least gets you in the right direction - Andrea  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Andrea Lynch

Expertise

I am very familiar with teaching excel concepts, and formulas. I am not as capable with VBA questions.

Experience

I currently tutor in this area. I worked for four years developing curriculum and teaching Microsoft applications to adult audiences. I also previously volunteered for allexperts.com.

Education/Credentials
BA, English, Western Washington University Certificates in C Programming, ASP.NET, VisualBasic.NET, University of Washington Extension

Awards and Honors
Rated in the top ten instructors (national), New Horizons Computer Learning Center.

©2016 About.com. All rights reserved.