Excel/Excel 2003 VBA

Advertisement


Question
Please recognize that the following represents just a small fraction of the macro I created, but it is enough to define the problem I'm having.

I'm using Excel 2003 VBA. I have written a procedure where I open a spreadsheet containing many records. There is some commonality between the records, but it is not such that I can put them in proper order for later processing. I wrote a procedure to take each record, and depending on certain criteria, assign a Group Code to the record. I then invoke a second subroutine called Sub ProcessRecords(GroupCode), passing to it the argument called "GroupCode" for additional processing on that record, including the actual assignment of the GroupCode.

I process each record with the following code:
   
Range("A1").Select

Do While ActiveCell <> ""

Select Case ActiveCell

   Case "LLC A"
       Set GroupCode = "R NDV"     (The Compile stops on this line)
       ProcessRecords GroupCode
     
   Case "LLC R"
       Set GroupCode = "D NDV"
       ProcessRecords GroupCode

   Case "LTK 7"
       Set GroupCode = "R DNV"
       ProcessRecords GroupCode

End Select

Loop

End Sub

------------------------------------------------------------------
Here's my problem: When I press F8 to run the procedure, I get a VBA Message that says "Compile Error: Object Required". This message appears on the line identified above. Of course, I can't go any further.

I have searched through the VBA Help file and my 870 page VBA book, but I can't find the solutionn. What am I doing wrong? What kind of "object" is required?

Interstingly I had used arguments in other programs which all worked successfully.

Thanks for your help.

Bob K.

Answer
The keyword SET is used to set an object variable to an instance of that object - so you could use
dim myWrd as object
Set MyWrd=application("Microsoft Word") 'not strictly correct syntax but gives the idea

In your case, you AREN'T using an object, so the word SET isn't needed - instead

GroupCode = "D NDV"

would set the contents of the variable GroupCode to the string "D NDV"

It should then hopefully behave itself.
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


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.