-
Sep 27th, 2017, 09:11 AM
#1
Thread Starter
Lively Member
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?
-
Sep 27th, 2017, 09:28 AM
#2
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.
-
Sep 27th, 2017, 09:49 AM
#3
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
-
Sep 27th, 2017, 09:53 AM
#4
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.
-
Sep 27th, 2017, 10:40 AM
#5
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
-
Sep 27th, 2017, 12:08 PM
#6
Re: Passing multiple variables to a routine
-
Sep 27th, 2017, 01:49 PM
#7
Thread Starter
Lively Member
Re: Passing multiple variables to a routine
Originally Posted by techgnome
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)
-
Sep 27th, 2017, 03:11 PM
#8
Re: Passing multiple variables to a routine
Originally Posted by martin_hop
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
-
Sep 27th, 2017, 03:52 PM
#9
Thread Starter
Lively Member
Re: Passing multiple variables to a routine
Originally Posted by Shaggy Hiker
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.
-
Sep 27th, 2017, 04:27 PM
#10
Thread Starter
Lively Member
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
-
Sep 28th, 2017, 02:06 AM
#11
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.
-
Sep 28th, 2017, 05:30 AM
#12
Re: Passing multiple variables to a routine
Martin
Chris might be on to something.
Could you post a screenshot of your ListBox
Spoo
-
Sep 28th, 2017, 07:15 AM
#13
Re: Passing multiple variables to a routine
Originally Posted by Shaggy Hiker
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.
-
Sep 28th, 2017, 07:19 AM
#14
Re: Passing multiple variables to a routine
Originally Posted by ChrisE
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
Originally Posted by Spooman
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
-
Sep 28th, 2017, 07:39 AM
#15
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|