Excel vba datatypes

Posted on 2011-04-30
Last Modified: 2012-05-11
I am little confused here.
Is 'worksheet' and 'range' a datatype?
I have an example code that I am working on.
Apparently there are variables declared witgh this datatype

Can any one please explain a little on this to help me understand it

Question by:SameerMirza
    LVL 50

    Accepted Solution


    worksheet and range are objects. You can declare variables for these objects and then assign values to them.

    Dim ws as worksheet
    Dim rng as Range

    Then you can assign values to these variables.

    Set ws = Worksheets("Sheet1")
    Set rng = ws.Range("A1")

    After that, you can use the rng variable to manipulate the range object's content

    rng = "hello world"

    The full code:

    Sub test()
    Dim ws As Worksheet
    Dim rng As Range
    Set ws = Worksheets("Sheet1")
    Set rng = ws.Range("A1")
    rng = "hello world"
    rng.Font.ColorIndex = 4
    End Sub

    Open in new window

    If you want to change what range the rng variable points to, you need to use the Set keyword. If you want to manipulate the properties of the range that the rng variable points to, e.g. content, formatting, etc., you use the rng variable like any other variable or like the object that the variable points to. So, instead of

    Worksheets("Sheet1").Range("A1").Font.ColorIndex = 4

    you can use

    rng.Font.ColorIndex = 4

    cheers, teylyn

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now