Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: Passing arrays to functions?

    I have to abstract code that will run for each sheet in my workbook, the code hides different colms and rows depending on the sheet.

    i also have a question about sheet names vs the Sheet1(sheetName) sheet1 i see in the microsoftExcelObjects in the project explorer

    i would like to have a sub that calls my abstracted sub but dont know how to create an array and how to pass and recieve it.


    sub main

    HideStuff sheetName, cols, rows
    end sub
    sub hideStuff

    sheet(sheetName).select

    for each c in cols
    Columns(c).Hidden=true ' Columns("A:E").Hidden=true
    ' Columns("H:L").Hidden=true
    ' Columns("p:q").Hidden=true
    next c

    for each r in rows
    Rows(r).Hidden=true
    ' Rows("1:3").Hidden=true
    ' Rows("30:32").Hidden=true
    next r

    End sub

    also instead of passing a sheet name that can change, how can i do this?

    sub main

    HideStuff sheet1, cols, rows
    end sub
    Sub HideStuff sheet1 as worksheet, cols, rows

    sheet1.select
    end sub

  2. #2
    Join Date
    Oct 2003
    Posts
    233
    ok this is as far as i am now, 2 issues, ok i need 2 cols & rows for the 1st sheet but how do i change this as needed for the other 24 sheets??

    Any hints or ideas apprecated

    Sub Board()
    With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False
    End With

    Dim myCols(2) As String
    Dim myRows(2) As String

    myCols.Add ("C:E")
    myCols.Add ("Q:AR")

    myRows.Add ("24:25")
    myRows.Add ("46:48")

    setUpBoardView sheet3, myCols, myRows, "a1..g55"


    With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .EnableEvents = True
    End With

    End Sub

    Sub setUpBoardView(sht As Worksheet, myCols() As String, myRows() As String, printRange As String)
    Dim i As Integer

    sht.Select

    For i = 0 To myCols.ubound 'ITHINK THIS IS WRONG ALSO
    Columns(myCols(i)).Hidden = True
    Next
    For i = 0 To myRows.ubound
    rows(myRows(i)).Hidden = True
    Next

    PageSetup printRange, xlPortrait, 1, 2, 0.75

    End Sub
    Sub PageSetup(sPrintArea As String, Orientation As Excel.XlPageOrientation, PagesWide As Integer, pagesTall As Integer, topMargin As Double)
    With ActiveSheet.PageSetup
    .PrintArea = sPrintArea
    .Orientation = Orientation
    .FitToPagesWide = PagesWide
    .FitToPagesTall = pagesTall
    .topMargin = Application.InchesToPoints(topMargin)
    End With
    End Sub

Posting Permissions

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