• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

VBA Worksheet Basics


I'm trying to get the basics of the objects, properties, and methods down, but can't find any great online resources.  In the meantime, I guess actual examples is the best way to go.  I have some code that I got from one of the experts here in a previous question (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26856102.html).  The problem was, in the example I provided, the data was on the same page, when I'm actually linking to and importing from an Access database onto a separate Sheet I call "QB_Expenses".  I'm trying to walk through the code so I can learn and adjust.  My first sticking point is:
 lastRow = Range("A" & Rows.Count).End(xlUp).Row

how do I change that so that I'm finding the last row on the QB_Expenses sheet instead of the current sheet?
0
BBlu
Asked:
BBlu
  • 4
  • 3
3 Solutions
 
rspahitzCommented:
you can try this:

 lastRow = Sheets("QB_Expenses").Range("A" & Rows.Count).End(xlUp).Row
0
 
BBluAuthor Commented:
got it.  so Sheets is the object, Range is a property (of Sheets). and an object? Are End and Row considered properties, methods?
0
 
rspahitzCommented:
Pretty much.

the way most things work these days is that objects have properties and possibly additional nested objects, which are treated like properties.

So from the Application object of Excel, you have the WorkBook objects, which have the WorkSheet objects, which have several objects such as the Range,Row and Column objects; the Range object has cells, etc.

Typically, and item that you see with parentheses after it is either an array (like Sheets) or a method (and action that is either a subroutine or a function) like the End method.  Any method can have zero or more parameter values such as xlUp for the End method.

If there are no parentheses then it's probably a property (although VB is careless about this so sometimes it's still a method without parameters.)
when working with properties, then you can usually assign it a value with = xxx (although some properties are read-only)

the easiest way to tell the difference is to let Vb help.  Type the first few letters of on of these things and press Ctrl+J; you should get an intellisense drop-down window.  If you see a little hand holding a paper, it's a property or embedded object (blue box in VB.Net); green box is a method (purple in VB.Net); yellow lightning bolt for an event.  you'll also see other symbols for things like libraries constants and enumerations, etc.

Hope that helps a little.

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BBluAuthor Commented:
Thanks, rspahitz.  That is the perfect explanation!
0
 
BBluAuthor Commented:
Before I close out this question, is there a way to keep the current sheet selected rather than switch when I perform the code:

 lastRow = Sheets("QB_Expenses").Range("A" & Rows.Count).End(xlUp).Row
0
 
rspahitzCommented:
I think that works behind the scenese (although some of Excel's method require that the sheet be active.

The best way to ensure that the current sheet is active when done is to save it, perform the action, then restore it.  In this case, you use the "object" syntax ("Set") of VB:

    Dim objSaveSheet As Worksheet
    Set objSaveSheet = ActiveSheet

' do what you need to
'e.g.    Sheets("Sheet2").Activate

    objSaveSheet.Activate
    Set objSaveSheet = Nothing
0
 
BBluAuthor Commented:
Great help, as always.  Thanks, rspahitz!
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now