VB in excel macros


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: VB in excel macros

  1. #1
    Dave C Guest

    VB in excel macros


    I have a macro that prints reports 1 to 48.

    Sub ADCHART()
    For X = 1 To 48
    Range("keyval").Value = X
    Calculate
    Calculate
    Calculate
    ActiveSheet.PrintOut copies:=1
    Next

    End Sub

    I would like to use this type of macro to print beyond 48, however, the reports
    are not in a continuous range. The numbers are between 2002 and 999999,
    but only 550 reports exist and I do not want to print all those blank pages.


    I recorded a macro using excel but it is so large, i had to split it in two.
    Here is a sample:

    Range("B63").Select
    ActiveCell.FormulaR1C1 = "2002"
    Range("B64").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range("B63").Select
    ActiveCell.FormulaR1C1 = "2034"
    Range("B64").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range("B63").Select
    ActiveCell.FormulaR1C1 = "2061"
    Range("B64").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range("B63").Select
    ActiveCell.FormulaR1C1 = "2062"
    Range("B64").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range("B63").Select
    ActiveCell.FormulaR1C1 = "22092"
    Range("B64").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1


    As you can see, there is a lot of code for such a simple job. is there any
    way to shrink this code into something like the original macro?

  2. #2
    Arthur Wood Guest

    Re: VB in excel macros


    Dave,

    If you know before hand what the 550 vlaues are, then create an array
    to hold the values

    Dim myValues(550) as Integer

    then you can set each of the array elements to the correct value, and then
    loop through the array just as you are already looping through the value
    1 to 48:

    For X = 1 To 550
    Range("B63").Select
    ActiveCell.FormulaR1C1 = myArray(X)
    Range("B64").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Next


    that should simplify things greatly.

    Arthur Wood



    "Dave C" <djcourtright@yahoo.com> wrote:
    >
    >I have a macro that prints reports 1 to 48.
    >
    >Sub ADCHART()
    > For X = 1 To 48
    > Range("keyval").Value = X
    > Calculate
    > Calculate
    > Calculate
    > ActiveSheet.PrintOut copies:=1
    > Next
    >
    >End Sub
    >
    >I would like to use this type of macro to print beyond 48, however, the

    reports
    >are not in a continuous range. The numbers are between 2002 and 999999,
    >but only 550 reports exist and I do not want to print all those blank pages.
    >
    >
    >I recorded a macro using excel but it is so large, i had to split it in

    two.
    > Here is a sample:
    >
    > Range("B63").Select
    > ActiveCell.FormulaR1C1 = "2002"
    > Range("B64").Select
    > ActiveWindow.SelectedSheets.PrintOut Copies:=1
    > Range("B63").Select
    > ActiveCell.FormulaR1C1 = "2034"
    > Range("B64").Select
    > ActiveWindow.SelectedSheets.PrintOut Copies:=1
    > Range("B63").Select
    > ActiveCell.FormulaR1C1 = "2061"
    > Range("B64").Select
    > ActiveWindow.SelectedSheets.PrintOut Copies:=1
    > Range("B63").Select
    > ActiveCell.FormulaR1C1 = "2062"
    > Range("B64").Select
    > ActiveWindow.SelectedSheets.PrintOut Copies:=1
    > Range("B63").Select
    > ActiveCell.FormulaR1C1 = "22092"
    > Range("B64").Select
    > ActiveWindow.SelectedSheets.PrintOut Copies:=1
    >
    >
    >As you can see, there is a lot of code for such a simple job. is there

    any
    >way to shrink this code into something like the original macro?



  3. #3
    Craig Brown Guest

    Re: VB in excel macros


    Dave,

    First, I would use Arthur's suggestion, below. It's simple, and, more importantly,
    easy to follow.

    If your list of 550 values is at all dynamic, i.e. maybe 550 today but 549
    tomorrow, maybe a different set of values in the 550, etc. I would put the
    values in a .txt file and read it in at start-up. That way you can modify
    the list without modifying the program.

    For X = 1 To UBound(TheListArray)
    ....See Arthur's Code below
    Next X

    Now your program/macro is "stupid", it doesn't know or care how many or what
    the values are, it just processes them.

    Craig Brown



    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >
    >Dave,
    >
    > If you know before hand what the 550 vlaues are, then create an array
    >to hold the values
    >
    > Dim myValues(550) as Integer
    >
    >then you can set each of the array elements to the correct value, and then
    >loop through the array just as you are already looping through the value
    >1 to 48:
    >
    > For X = 1 To 550
    > Range("B63").Select
    > ActiveCell.FormulaR1C1 = myArray(X)
    > Range("B64").Select
    > ActiveWindow.SelectedSheets.PrintOut Copies:=1
    > Next
    >
    >
    >that should simplify things greatly.
    >
    >Arthur Wood
    >
    >
    >
    >"Dave C" <djcourtright@yahoo.com> wrote:
    >>
    >>I have a macro that prints reports 1 to 48.
    >>
    >>Sub ADCHART()
    >> For X = 1 To 48
    >> Range("keyval").Value = X
    >> Calculate
    >> Calculate
    >> Calculate
    >> ActiveSheet.PrintOut copies:=1
    >> Next
    >>
    >>End Sub
    >>
    >>I would like to use this type of macro to print beyond 48, however, the

    >reports
    >>are not in a continuous range. The numbers are between 2002 and 999999,
    >>but only 550 reports exist and I do not want to print all those blank pages.
    >>
    >>
    >>I recorded a macro using excel but it is so large, i had to split it in

    >two.
    >> Here is a sample:
    >>
    >> Range("B63").Select
    >> ActiveCell.FormulaR1C1 = "2002"
    >> Range("B64").Select
    >> ActiveWindow.SelectedSheets.PrintOut Copies:=1
    >> Range("B63").Select
    >> ActiveCell.FormulaR1C1 = "2034"
    >> Range("B64").Select
    >> ActiveWindow.SelectedSheets.PrintOut Copies:=1
    >> Range("B63").Select
    >> ActiveCell.FormulaR1C1 = "2061"
    >> Range("B64").Select
    >> ActiveWindow.SelectedSheets.PrintOut Copies:=1
    >> Range("B63").Select
    >> ActiveCell.FormulaR1C1 = "2062"
    >> Range("B64").Select
    >> ActiveWindow.SelectedSheets.PrintOut Copies:=1
    >> Range("B63").Select
    >> ActiveCell.FormulaR1C1 = "22092"
    >> Range("B64").Select
    >> ActiveWindow.SelectedSheets.PrintOut Copies:=1
    >>
    >>
    >>As you can see, there is a lot of code for such a simple job. is there

    >any
    >>way to shrink this code into something like the original macro?

    >



  4. #4
    Peter Higgns Guest

    Re: VB in excel macros


    I find it unclear from your explanation just what you are printing. I presume
    it is the same sheet using different sets of starting data. You could place
    the numbers in another sheet and determine the end using cells.currentregion.specialcells(xlcelltypelastcell)
    or use the function below,

    Public Function GetLastRow() As Long
    GetLastRow = ActiveSheet.Cells.CurrentRegion.SpecialCells(xlCellTypeVisible).Rows.Count
    End Function


    or you could build a collection, or variant array(shown) and use for next
    AnArray = array(2002,2034, 2061,... )
    for each VItem in AnArray

    Hope this helps

    "Dave C" <djcourtright@yahoo.com> wrote:
    >
    >I have a macro that prints reports 1 to 48.
    >
    >Sub ADCHART()
    > For X = 1 To 48
    > Range("keyval").Value = X
    > Calculate
    > Calculate
    > Calculate
    > ActiveSheet.PrintOut copies:=1
    > Next
    >
    >End Sub
    >
    >I would like to use this type of macro to print beyond 48, however, the

    reports
    >are not in a continuous range. The numbers are between 2002 and 999999,
    >but only 550 reports exist and I do not want to print all those blank pages.
    >
    >
    >I recorded a macro using excel but it is so large, i had to split it in

    two.
    > Here is a sample:
    >
    > Range("B63").Select
    > ActiveCell.FormulaR1C1 = "2002"
    > Range("B64").Select
    > ActiveWindow.SelectedSheets.PrintOut Copies:=1
    > Range("B63").Select
    > ActiveCell.FormulaR1C1 = "2034"
    > Range("B64").Select
    > ActiveWindow.SelectedSheets.PrintOut Copies:=1
    > Range("B63").Select
    > ActiveCell.FormulaR1C1 = "2061"
    > Range("B64").Select
    > ActiveWindow.SelectedSheets.PrintOut Copies:=1
    > Range("B63").Select
    > ActiveCell.FormulaR1C1 = "2062"
    > Range("B64").Select
    > ActiveWindow.SelectedSheets.PrintOut Copies:=1
    > Range("B63").Select
    > ActiveCell.FormulaR1C1 = "22092"
    > Range("B64").Select
    > ActiveWindow.SelectedSheets.PrintOut Copies:=1
    >
    >
    >As you can see, there is a lot of code for such a simple job. is there

    any
    >way to shrink this code into something like the original macro?



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center