Auto Format in Excel using VBScript

Posted on 2009-02-16
Last Modified: 2012-05-06
I'm looking for some code for a VB Script so I can autoformat a worksheet in excel.  I tried using the macro recorder and got the following code but I can't get it to run properly.  
It doesn't have to use the code I have below I am just looking for somethign so I can format my worksheets.  
"ie" is Set ie = CreateObject("excel.Application")

ie.Selection.AutoFormat Format:= xlRangeAutoFormatClassic3, Number:=True, Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

Open in new window

Question by:Droo1977
    LVL 92

    Expert Comment

    by:Patrick Matthews
    ie.Selection.AutoFormat Format:=3, Number:=True, Font:=True, Alignment:=True, Border:=True, _
        Pattern:=True, Width:=True

    xlRangeAutoFormatClassic3 is equal to 3; in VBScript you cannot use named constants from other libraries.
    To get the value for any Excel named constant, just go to Excel's VB Editor, and run something like this in
    the Immediate window:


    Author Comment

    Thanks, I've actually tried that and it gives me an "Expected Statement"  error. What else might I be doing wrong?
    LVL 92

    Accepted Solution

    Sorry, I forgot that VBScript also does not support passing arguments by name--you can only pass them
    by position:

    ie.Selection.AutoFormat 3, True, True, True, True, True, True

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    755 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

    16 Experts available now in Live!

    Get 1:1 Help Now