help! i'm new to using VBA with excel codes...

[Angel_Newbie]

help people! i'm new to programming VB with excel data's... i'm currrently doing a program about a Hotline Log system in which the telephone operators log-in the time of the calls and also the time when the job was started and when it was finished. then they get the difference of the job finished and job started and logs it in the MTTR field (i forgot what MTTR stands for ) what i need to do is to return the values showing the total MTTR per day and also the total MTTR per owner(the technician who did the job) per day.

here's a part of the code which i can't figure out why i can't get the listbox to show the details.. is there something wrong with it? i'd appreciate if you guys would teach me on how to go about this problem... thanks!


--------------------
Public appExcel As Object
--------------

Private Sub Form_Initialize()

Dim wsheet As excel.Worksheet
Dim wbook As excel.Workbook


On Error Resume Next
Set appExcel = GetObject("C:\My Documents\Sample program\Sample1.xls") ', "Excel.Application")
Set wbook = appExcel.Application.Workbooks("sample1.xls")
Set wsheet = wbook.Sheets("SHEET1")
max_row = wsheet.UsedRange.Rows.Count
max_col = wsheet.UsedRange.Columns.Count

wbook.Windows(1).Visible = True
wbook.Application.Visible = False
Dim MTTRvalue As String

'selects the values on range a starting with row2
curRow = 2
wsheet.Range("A" & curRow).Select
curDate = wsheet.Range("A" & curRow).Value
curRow = curRow + 1

'checks if the values of the dates are thesame
While curDate <> ""
wsheet.Range("A" & curRow).Select
edate = wsheet.Range("A" & curRow).Value

'if the values of the dates are thesame, the calculation is then performed and the total MTTR is being displayed in the list box together with the date

If edate = curDate Then
MTTRtotal = MTTRtotal + wsheet.Range("F" & curRow).Value
Else
MTTRvalue$ = Format$(MTTRtotal, "#.00")
MTTRvalue$ = Space(12 - Len(MTTRvalue$)) & MTTRvalue
Listbox1.AddItem edate & Chr(9) & Chr(9) & MTTRvalue$
MTTRtotal = wsheet.Range("F" & curRow).Value
curDate = edate
InvCounter = InvCounter + 1
End If

Wend
End Sub

[opus]

What do you see in your ListBox, only one Value?
I think you don'T do any looping over all rows!

[WorkHorse]

Yep. You need to have the code to check the next row also inside the loop.

'selects the values on range a starting with row2 
curRow = 2 
wsheet.Range("A" & curRow).Select 
curDate = wsheet.Range("A" & curRow).Value 
curRow = curRow + 1 
 
'checks if the values of the dates are thesame 
While curDate <> "" 
wsheet.Range("A" & curRow).Select 
edate = wsheet.Range("A" & curRow).Value 
 
'if the values of the dates are thesame, the calculation is then performed and the total MTTR is being displayed in the list box together with the date 
 
If edate = curDate Then 
MTTRtotal = MTTRtotal + wsheet.Range("F" & curRow).Value 
Else 
MTTRvalue$ = Format$(MTTRtotal, "#.00") 
MTTRvalue$ = Space(12 - Len(MTTRvalue$)) & MTTRvalue 
Listbox1.AddItem edate & Chr(9) & Chr(9) & MTTRvalue$ 
MTTRtotal = wsheet.Range("F" & curRow).Value 
curDate = edate 
InvCounter = InvCounter + 1 
End If 
 
'*******This needs to be in the loop.
wsheet.Range("A" & curRow).Select 
curDate = wsheet.Range("A" & curRow).Value 
curRow = curRow + 1 
 
Wend

[Angel_Newbie]

actually i just copied and edited the codes from a book... got it all figured out just today... the only problem is there's always this kind of error:Run-time error '424' --- object required

here are the original codes which i had copied from the book:

'---------------------module1-----------------
Public appExcel As Object

Sub Main()
On Error Resume Next
Set appExcel = GetObject (App.Path & "\invoice.xls", "Excel.Application")

If appExcel Is Nothing Then
Set appExcel = CreateObject("Excel.Application")
appExcel.workbooks.open App.Path & "\invoice.xls"
End If
Form1.Show
End Sub


--------------userform1 (imported from excelvba to visual basic)---------

Private Sub UserForm_Initialize()
Dim InvoiceValue As String

curRow = 2
'this line always gets highlighted when i run/debug the program:
appExcel.Sheets("sheet1").Range("A" & curRow).Select
curInvoice = appExcel.ActiveCell.Value
curRow = curRow + 1
While curInvoice <> ""
appExcel.Sheets("sheet1").Range("A" & curRow).Select
InvoiceNumber = appExcel.ActiveCell.Value
If InvoiceNumber = curInvoice Then
invoicetotal = invoicetotal + appExcel.Sheets("sheet1").Range("C" & curRow).Value
Else
InvoiceValue$ = Format$(invoicetotal, "#.00")
InvoiceValue$ = Space(12 - Len(InvoiceValue$)) & InvoiceValue
ListBox1.AddItem InvoiceNumber & Chr(9) & Chr(9) & InvoiceValue$
invoicetotal = appExcel.Sheets("sheet1").Range("C" & curRow).Value
curInvoice = InvoiceNumber
invcounter = invcounter + 1
End If
Form1.Label1.Caption = "Processing invoice line#" & curRow
Form1.Label2.Caption = "Number of Invoices" & invcounter
curRow = curRow + 1
Form1.Refresh

Wend
DoEvents
End Sub


things were running well when i did it in the excelVBA editor but then when i exported the form to visual basic, things didn't seem to go pretty well... pls help! thanks!

[opus]

Looks like your appExcel hasn'T been opened. Is EXcel open when you see the error, I guess not.
And you can't use "UserForm_Initialize" in VB. I guess it should be named "Form1_Load"

[WorkHorse]

My guess is that appExcel isn't being set because Sub Main is never called. VB Projects by default start up with the form. In VB, select "Project" from the menu, then select the last option to show your project's properties. On the "General" tab there is a field for StartUp Object. Set that to Sub Main. Now when your code runs it will run Sub Main first.

As opus mentioned, the standard controls in VB are different from the controls available in VBA. The VBA controls are called Microsoft Forms 2.0 controls. They have some different properties and events from the VB controls. If you loaded a VBA UserForm as a designer, you can use the Forms 2.0 controls (although you may need to add the Forms 2.0 Controls Library as a component in your project). If you created a new form in VB, you will have to change some of the event subs. Programers don't usually use Forms 2.0 controls in VB projects (they aren't standard, there can be licensing issues for distribution, and the controls sometimes don't work real well in VB).

[Angel_Newbie]

Thanks. umm actually i had got the program running in VBA... the only trouble i have is that i can't seem to align the values right... the 2nd column on the list should show the value in row 2 but instead it shows the value in row1 but then the 1st column displays the right values which starts with row2.

here's the code that i made in VBA... hopefully i could also get it running in visual basic without exporting the user form in vba... iwas trying to fix the codes in visual basic but when i start to run the program, it begins to hang.

here's the code in VBA:

Private Sub UserForm_Initialize()
Dim InvoiceValue As String

curRow = 1
Range("A" & curRow).Select
curInvoice = ActiveCell.Value
'curRow = curRow + 1
While curInvoice <> ""
Range("A" & curRow).Select
InvoiceNumber = ActiveCell.Value
If InvoiceNumber = curInvoice Then
invoicetotal = invoicetotal + Range("f" & curRow).Value
Else
InvoiceValue$ = Format$(invoicetotal, "#.00")
InvoiceValue$ = Space(12 - Len(InvoiceValue$)) & InvoiceValue
ListBox1.AddItem InvoiceNumber & Chr(9) & Chr(9) & InvoiceValue$
invoicetotal = Range("f" & curRow).Value
curInvoice = InvoiceNumber

End If
curRow = curRow + 1


Wend
DoEvents
End Sub