Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: setting print area from within a function

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following function should set the print area as a function of column number:

    ---------------------------------------------
    Function setprintarea(ByVal column As Integer) As String

    ' Sets the print area to range "A1:colstring"
    Dim colstring As String
    colstring = Chr((column Mod 26 + 65)) & "$35" 'last or only char and row number
    If Int(column / 26) >= 1 Then colstring = Chr((Int(column / 26) + 64)) & colstring 'higher order
    colstring = "$A$1:" & "$" & colstring
    setprintarea = colstring 'returns range
    Activesheet.PageSetup.printarea = colstring
    End Function
    ---------------------------------------------
    The column number is converted to a string and returned for info purposes. The next line should set the print area. This works when used within a sub program locally within VBA, but not from within the function or in the sub from the worksheet. Is there a restriction on these procedures from within functions? The sub name was not found when entered into a cell, thus the function attempt.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can't change the environment using a function, functions only returns values. You'll have to use a Sub (Or this function and use the result in a Sub)
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •