Results 1 to 15 of 15

Thread: Passing multiple variables to a routine

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    68

    Passing multiple variables to a routine

    Hi All,

    I have a routine that creates a spreadsheet, currently this spreadsheet creates three tabs (Header, Summary, Schedule) based off a single quote. What i want to do is create multiple schedule tabs based on the users selection in a list box. The way the interface is designed I had to create a new form that filters the schedules that the users can choose from based on the current job. My question is how can i get all the selected schedules (stored in a listbox on the new form, EG. Schedule_1, Schedule_2, Schedule_3 etc) from that form to the spreadsheet routine so i can loop through the schedule part of the routine only. if i passed each schedule through individually via a loop from the new form this would create a spreadsheet for each selected item. I think what i need to do if possible is pass all select schedules through so i can loop only the schedule part of the spreadsheet based on the user input rather than looping the whole routine. Firstly is this possible, secondly if so what would be my best appraoch, lastly if not possible how could i work around this?

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Passing multiple variables to a routine

    Hi Martin,

    I do a great deal of Excel automation from VB6. Obviously, your head is well wrapped around the specific problem you're dealing with. And I'm not totally clear that I understand all your specifics. However, let me see if I can generalize your question...

    You'd like to be able to create new Excel tabs (worksheets) from VB6, correct? And then, from there, you can manipulate your Excel file however you like.

    This is not something I often do. However, if I were "teaching myself" how to do this, I'd just record a macro from Excel to "teach myself". And then, I'd adapt that macro to Excel. I'll try it here and post the results momentarily.

    Also, it'd be nice if we had a snippet of code where you're automating Excel from VB6. That way, I could use variable names similar to the ones you're using. Also, I don't know if you're starting from an Excel template, or just starting from scratch.

    Best Regards,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  3. #3
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: Passing multiple variables to a routine

    Martin

    In addition to Elroy's comments, here is a code frag to pass multiple variables (aka parameters)
    from one sub to another

    Code:
    Sub Command1_Click()
        x = 3
        y = "text 1"
        z = "text 2"
        ' call sub, pass parameters
        ExcelCreate x, y, z
        '
    End Sub
    '
    Sub ExcelCreate(x As Integer, y as String, z As String)
        < do stuff here >
    End Sub
    Natch, modify as needed.
    The calling sub, Command1, could just as easily be List1

    HTH
    Spoo

  4. #4
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Passing multiple variables to a routine

    Okay, I did this all in VBA, but it should be easy to convert to VB6.

    Code:
    
    Sub Testing()
        Dim xls As Object
        Dim wbk As Object
        '
        Set xls = Application           ' This must be different in VB6.
        Set wbk = xls.ActiveWorkbook    ' This will also be different in VB6.
    
    
        AddNewSheetToEnd wbk, "MyNewSheet 1"
        AddNewSheetToEnd wbk, "MyNewSheet 2"
        AddNewSheetToEnd wbk, "MyNewSheet 3"
    
    End Sub
    
    
    
    Public Sub AddNewSheetToEnd(wbk As Object, sSheetName As String)
        Dim shts As Object
        Dim sht As Object
        '
        Set shts = wbk.Sheets
        Set sht = shts.Add(, shts(shts.Count))
        sht.Name = sSheetName
    End Sub
    
    Enjoy,
    Elroy

    EDIT1: Also, I just did it all with late binding, because that's typically how I do things in VB6.

    EDIT2: Also, after re-reading the thread title, maybe I'm mis-understanding. Heck, you can just pass the whole listbox to some procedure if you like. I do that all the time. And then, read the listbox from the receiving procedure. You don't even need to worry about the form ... just pass the listbox.
    Last edited by Elroy; Sep 27th, 2017 at 10:00 AM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Passing multiple variables to a routine

    The way I read it is that the OP has a sub that handles the whole thing end-to-end... creates and opesn the workbook, then adds a sheet to it with the name, saves it and closes it. Now he's got a list where the user can multi-select, if he loops and calls it as it is, then it create multiple workbooks with a single sheet ... what he wants is a single workbook with multiple sheets. To me it's simple. Pass in an array... in the sub, create the work book, loop through the array, create the sheets as needed, add them, save the work book, exit loop, close workbook, exit sub.
    Should be fairly minimal changes needed.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Passing multiple variables to a routine

    What's an array?

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    68

    Re: Passing multiple variables to a routine

    Quote Originally Posted by techgnome View Post
    The way I read it is that the OP has a sub that handles the whole thing end-to-end... creates and opesn the workbook, then adds a sheet to it with the name, saves it and closes it. Now he's got a list where the user can multi-select, if he loops and calls it as it is, then it create multiple workbooks with a single sheet ... what he wants is a single workbook with multiple sheets. To me it's simple. Pass in an array... in the sub, create the work book, loop through the array, create the sheets as needed, add them, save the work book, exit loop, close workbook, exit sub.
    Should be fairly minimal changes needed.

    -tg
    Sorry for late reply got called away. You have it spot on, been thinking whilst in gym (how sad but i find walking away helps at times) a array will do the job. (As you say creating the excel spreadsheet is sorted, really wouldn't want to re-write that as it's about 2000 lines of code.)

    Could someone give me and example how i load all the items in the listbox into the array. Think i'm being dumb here but i created a dynamic array but cannot get the data into the array without an error. Not sure why arrays have alway been my nemesis (although in this instance i think its more the way i'm accessing the data in the listbox). I currently have something similar to the code below (away from work computer so doing it from memory).

    It errors on
    Code:
    QuoteToExport(j) = LstSelectedQuotes.List(i, 1)
    i think this is because i'm using one listbox (that contains all available quotes) to populate the listbox for just the quotes the users want to export. They then click a command button at which point i need to populate the array but there isn't actually nothing selected in the listbox.

    Code:
    Dim QuoteToExport() As String
    Dim Count As Integer, i As Integer, j As Integer
    Count = 0
    
    For i = 0 To LstSelectedQuotes.ListCount - 1
        If LstSelectedQuotes.Selected(i) Then Count = Count + 1
    Next i
    
    'based on the above count declare the array
    ReDim QuoteToExport(Count)
    
    j = 0
    For i = 0 To LstSelectedQuotes.ListCount - 1
        If LstSelectedQuotes.Selected(i) Then
            QuoteToExport(j) = LstSelectedQuotes.List(i, 1)
            j = j + 1
        End If
    Next i
    
    'Check values stored in array
    For i = 0 To Count - 1
    
        MsgBox QuoteToExport(i)

  8. #8
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Passing multiple variables to a routine

    Quote Originally Posted by martin_hop View Post
    been thinking whilst in gym (how sad but i find walking away helps at times)
    There's nothing sad about that, it's a solution with ancient roots. If you are stuck on a problem, go do something else. Your mind is still kicking over the problem, whether you are aware of it, or not. When you focus on a problem, your vision narrows to see the problem from just one angle. Taking a break is an excellent way to let your mind wander, at which point you will start getting connections to other problems, and will start seeing the current problem from a different perspective. In general, I'm not sure that hitting the gym is necessarily ideal, but it depends entirely on what you are doing there. I spent an hour swimming laps this morning, and saw a problem I was working on from an entirely different perspective, which I'll put into code tomorrow. On the other hand, I find that my brain doesn't work all that well with vigorous exercise, so walking, swimming, and so on seem good, but I'm not so sure about running up stairs, or things like that.

    However, I'm not clear: Is this really VB6, or VBA? It looks like it could be either. If it's straight VBA, I'll move it to Office Development, as you might get a different perspective from there. Not saying you'd get BETTER answers, though, as it seems you've gotten a few.
    My usual boring signature: Nothing

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    68

    Re: Passing multiple variables to a routine

    Quote Originally Posted by Shaggy Hiker View Post
    There's nothing sad about that, it's a solution with ancient roots. If you are stuck on a problem, go do something else. Your mind is still kicking over the problem, whether you are aware of it, or not. When you focus on a problem, your vision narrows to see the problem from just one angle. Taking a break is an excellent way to let your mind wander, at which point you will start getting connections to other problems, and will start seeing the current problem from a different perspective. In general, I'm not sure that hitting the gym is necessarily ideal, but it depends entirely on what you are doing there. I spent an hour swimming laps this morning, and saw a problem I was working on from an entirely different perspective, which I'll put into code tomorrow. On the other hand, I find that my brain doesn't work all that well with vigorous exercise, so walking, swimming, and so on seem good, but I'm not so sure about running up stairs, or things like that.

    However, I'm not clear: Is this really VB6, or VBA? It looks like it could be either. If it's straight VBA, I'll move it to Office Development, as you might get a different perspective from there. Not saying you'd get BETTER answers, though, as it seems you've gotten a few.
    It is meant to be vb6 but i pinched the basis of this code above offline so may have accidentally pulled VBA code.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    68

    Re: Passing multiple variables to a routine

    Ok so was bugging me and knew i wouldn't sleep until solved. Currently i have the following code i'm going to implement initially to populate the array that i can pass to my excel routine. I'm sure there is a better way to populate the array from the listbox rather than having to select each listindex item but it works for now and allows me to get it up and running.

    Code:
    Dim QuoteToExport() As String
    Dim Count As Integer, i As Integer, j As Integer
    Count = 0
    
    For i = 0 To lstTest.ListCount - 1
        Count = Count + 1
        'If lstTest.Selected(i) Then Count = Count + 1
    Next i
    
    'based on the above count declare the array
    ReDim QuoteToExport(Count)
    
    j = 0
    For i = 0 To lstTest.ListCount - 1
        lstTest.ListIndex = i
        If lstTest.Selected(i) Then
            QuoteToExport(j) = lstTest.Text
            j = j + 1
        End If
    Next i
    
    'Check values stored in array
    For i = 0 To Count - 1
    
        MsgBox "Array Count " & i & " = " & QuoteToExport(i)
    Next i

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Passing multiple variables to a routine

    Hi Martin,

    why not use a Flexgrid for the array, it has the same schema as excel ...row and cols

    here a sample
    Code:
    Public Function GetArrayFromFlex(Flex As MSFlexGrid) As String()
       Dim i As Long, j As Long
       Dim s() As String
          With Flex
             'in Array
             ReDim s(.Rows - 1, .Cols - 1)
             For i = 0 To .Rows - 1
                For j = 0 To .Cols - 1
                   s(i, j) = .TextMatrix(i, j)
                Next
             Next
          End With
          GetArrayFromFlex = s()
    End Function
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: Passing multiple variables to a routine

    Martin

    Chris might be on to something.
    Could you post a screenshot of your ListBox

    Spoo

  13. #13
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Passing multiple variables to a routine

    Quote Originally Posted by Shaggy Hiker View Post
    There's nothing sad about that, it's a solution with ancient roots. If you are stuck on a problem, go do something else. Your mind is still kicking over the problem, whether you are aware of it, or not. When you focus on a problem, your vision narrows to see the problem from just one angle.
    LOL..
    I get out the Lawn mower..

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  14. #14
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Passing multiple variables to a routine

    Quote Originally Posted by ChrisE View Post
    Hi Martin,

    why not use a Flexgrid for the array, it has the same schema as excel ...row and cols

    here a sample
    Code:
    Public Function GetArrayFromFlex(Flex As MSFlexGrid) As String()
       Dim i As Long, j As Long
       Dim s() As String
          With Flex
             'in Array
             ReDim s(.Rows - 1, .Cols - 1)
             For i = 0 To .Rows - 1
                For j = 0 To .Cols - 1
                   s(i, j) = .TextMatrix(i, j)
                Next
             Next
          End With
          GetArrayFromFlex = s()
    End Function
    regards
    Chris
    Quote Originally Posted by Spooman View Post
    Martin

    Chris might be on to something.
    Could you post a screenshot of your ListBox

    Spoo
    Why? He's already got a listbox with things selected... just get the .SelectedItems, create an array, loop through .SelectedItems, add each item, then pass the array to the sub... Why is everyone trying to make this harder than it needs to be?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Passing multiple variables to a routine

    Hi Tg,

    Why? He's already got a listbox with things selected... just get the .SelectedItems, create an array, loop through .SelectedItems, add each item, then pass the array to the sub... Why is everyone trying to make this harder than it needs to be?
    -tg
    because things never stay with just a Listbox and a single entry

    here a sample... might be useful for others

    in a Modul..
    Code:
    Option Explicit
    
    'Exelsheet füllen über ein 2-dimensionales StringArray
    Public Function ExcelFillFromArray(ExcelFile As String, _
                                       sArray() As String, _
                                       Optional FirstRow As Long = 1, _
                                       Optional FirstCol As Long = 1, _
                                       Optional Sheet As Variant = 1, _
                                       Optional Caption As String = vbNullString, _
                                       Optional ErrNumber As Long = 0, _
                                       Optional ErrDescription As String = vbNullString, _
                                       Optional ShowError As Boolean = True) As Boolean
    
       'hier Verweis auf Excel setzen
    '   Dim xclApp As Excel.Application
    '   Dim xclWbk As Excel.Workbook
    '   Dim xclSht As Excel.Worksheet
    
       'oder hier für Late Binding ohne Verweis
       Dim xclApp As Object
       Dim xclWbk As Object
       Dim xclSht As Object
    
       Dim i As Long, j As Long, LastCol As Long
       Dim CreateWorkBook As Boolean
       Dim CreateSheet As Boolean
       Dim Msg As String
       Dim s As String
    
          On Error GoTo Fehler
    
          'existiert die ExcelFile bereits
          If Len(Dir(ExcelFile)) = 0 Then
             s = ExcelFile
             i = InStrRev(s, "\")
             If i > 0 Then
                s = Left(ExcelFile, i - 1)
             End If
             'Pfad überprüfen
             If (Len(Dir(s, vbDirectory))) = 0 Then
                Err.Raise 76
             End If
             'Datei existiert noch nicht, muss angelegt werden
             CreateWorkBook = True
          Else
             'checken ob Datei von anderer Anwendung geöffnet
             If FileIsOpened(ExcelFile) Then
                Msg = ExcelFile & vbCrLf & vbCrLf & _
                      "ist von einer anderen Anwendung" & vbCrLf & _
                      "geöffnet und kann nicht geändert werden" & Space(10)
                ErrNumber = 55
                ErrDescription = Msg
                If ShowError Then
                   MsgBox Msg, vbCritical, "ExcelFillFromArray"
                End If
                Exit Function
             End If
          End If
          
          'Excel Object zeugen
          Set xclApp = CreateObject("Excel.Application")
          If CreateWorkBook Then
             'Neuanlage einer Excel File
             Set xclWbk = xclApp.Workbooks.Add
          Else
             'bestehende Excel File öffnen
             Set xclWbk = xclApp.Workbooks.Open(ExcelFile)
          End If
          
          'Sheet vorhanden oder nicht
          If IsNumeric(Sheet) Then
             'ist die SheetNummer grösser als Sheets vorhanden
             If Sheet > xclWbk.Sheets.Count Then
                'benötigte Sheet hinten anfügen
                For i = xclWbk.Sheets.Count + 1 To Sheet
                   xclWbk.Sheets.Add , xclWbk.Sheets(xclWbk.Sheets.Count)
                Next
                CreateSheet = True
             End If
            
          Else
             'SheetName überprüfen
             CreateSheet = True
             For i = 1 To xclWbk.Sheets.Count
                If xclWbk.Sheets(i).Name = Sheet Then
                   'Sheet gefunden
                   CreateSheet = False
                   Exit For
                End If
             Next
             If CreateSheet Then
                'Sheet nicht gefunden, hinten anfügen und benennen
                xclWbk.Sheets.Add , xclWbk.Sheets(xclWbk.Sheets.Count)
                xclWbk.Sheets(xclWbk.Sheets.Count).Name = Sheet
             End If
          End If
          Set xclSht = xclWbk.Worksheets(Sheet)
          
          With xclSht
             If (Not CreateWorkBook) And (Not CreateSheet) Then
                'bei bestehendem Tab Cells löschen
                .Cells.Clear
             End If
             
             'Überschrift setzen
             If Len(Caption) > 0 Then
                .Range("A1:A1").FormulaR1C1 = Caption
                'Überschrift fett + grösser
                .Range("A1:A1").Font.Bold = True
                .Range("A1:A1").Font.Size = .Range("A1:A1").Font.Size + 2
             End If
             
             'Grenzen des 2-dimensionalen Arrays und
             'Arbeitsbereich ermitteln
             i = UBound(sArray) + FirstRow
             j = UBound(sArray, 2) + FirstCol
             
             'Array in Arbeitsbereich kopieren
             .Range(.Cells(FirstRow, FirstCol), .Cells(i, j)).Value = sArray
             
             'Spaltenbeschriftung Fett
             .Range(.Cells(FirstRow, FirstCol), .Cells(FirstRow, j)).Font.Bold = True
          End With
          
          If CreateWorkBook Then
             'neues Workbook unter dem Namen sichern
             xclWbk.SaveAs Filename:=ExcelFile
          End If
          
          'keine Rückfragen
          xclApp.DisplayAlerts = False
          'Workbook schliessen mit speichern
          xclApp.ActiveWindow.Close SaveChanges:=True
          'Speicher freigeben
          Set xclSht = Nothing
          Set xclWbk = Nothing
          'Applikation beenden
          xclApp.Quit
          'Speicher freigeben
          Set xclApp = Nothing
          
          On Error GoTo 0
          ExcelFillFromArray = True
          Exit Function
          
    Fehler:
          ErrNumber = Err.Number
          ErrDescription = Err.Description
          If ShowError Then
             FehlerAnzeige Err.Number, Err.Description, "ExcelFillFromArray"
          End If
          On Error GoTo 0
    End Function
    
    'prüft ob auf dem Rechner Excel installiert ist
    Public Function IsExcelInstalled() As Boolean
       Dim xclApp As Object
          'Excel Object zeugen
          On Error GoTo Fehler
          Set xclApp = CreateObject("Excel.Application")
          IsExcelInstalled = True
          xclApp.Quit
          'Speicher freigeben
          Set xclApp = Nothing
    Fehler:
          On Error GoTo 0
    End Function
    Private Function FileIsOpened(Filename As String) As Boolean
       Dim FNr As Integer
          If Len(Dir(Filename)) = 0 Then
             Exit Function
          End If
          On Error Resume Next
          FNr = FreeFile
          Open Filename For Input Lock Read Write As #FNr
          If Err.Number <> 0 Then
             FileIsOpened = True
          Else
             Close #FNr
          End If
          On Error GoTo 0
    End Function
    
    Private Sub FehlerAnzeige(ErrNumber As Long, ErrDescription As String, _
                             Optional Titel As String = "")
       Dim Msg As String
          Msg = "Fehler " & ErrNumber & vbCrLf & vbCrLf & _
                ErrDescription
          If Len(Titel) > 0 Then
             MsgBox Msg, vbCritical, Titel
          Else
             MsgBox Msg, vbCritical
          End If
    End Sub
    in the Form..
    Code:
    Option Explicit
    
    Private Sub Command1_Click()
    'Fill Grid with some Data
       Dim i As Long, j As Long
          With MSFlexGrid1
             .Rows = 30
             .Cols = 5
             .FixedRows = 1
             .FixedCols = 0
             .Clear
             For j = 0 To .Cols - 1
                .TextMatrix(0, j) = "Spalte " & j
             Next
             For i = 1 To .Rows - 1
                For j = 0 To .Cols - 1
                   .TextMatrix(i, j) = "Cell " & i & "." & j
                Next
             Next
          End With
    End Sub
    
    Private Sub Command2_Click()
       Dim s() As String
       Dim Caption As String
       Dim TabName As String
       Dim ExcelFile As String
        ExcelFile = "c:\Test\test222.xls"
          Caption = "Header Created " & Format(Now, "dd.mm.yyyy hh:nn:ss") 'add a Header
          TabName = "mySheet"
          Me.MousePointer = vbHourglass
          s = GetArrayFromFlex(MSFlexGrid1)
          'Create as new sheet and call it
          'start adding Data from Row 5 and Col5 = E5 in Excel
          ExcelFillFromArray ExcelFile, s(), 5, 5, TabName, Caption
          Me.MousePointer = vbDefault
    End Sub
    
    Public Function GetArrayFromFlex(Flex As MSFlexGrid) As String()
       Dim i As Long, j As Long
       Dim s() As String
          With Flex
             'in Array
             ReDim s(.Rows - 1, .Cols - 1)
             For i = 0 To .Rows - 1
                For j = 0 To .Cols - 1
                   s(i, j) = .TextMatrix(i, j)
                Next
             Next
          End With
          GetArrayFromFlex = s()
    End Function

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

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



Click Here to Expand Forum to Full Width