using a function inside a sub (VBA using Word and Excel)

Question

I have a question about using a private function inside a private sub (command button).

It doesn't return any errors... nor does it do anything. When I press the command button in Word doc, it is supposed to form rows and import data from an Excel worksheet until the last row in Excel, which is what I'm trying to get the function to do - find the last row in the worksheet.

If you can look at my code and let me know if you know why it won't work, I'd appreciate it. Do I need to have the function inside the Private Sub commandbutton_2_Click()? Thank you in advance.

Private Sub CommandButton2_Click()
Dim tbl As Table
Dim row As row

Set tbl = ActiveDocument.Tables(2)

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
On Error Resume Next

Set exWb = objExcel.Workbooks.Open("S:\Electro-Protocol\Mot_Protocols\" & TextBox1 & ".xls")
Dim lastRow As Integer

lastRow = GetLastRow(objExcel, exWb)
ActiveDocument.Tables(2).Columns.DistributeWidth

For counter = 1 To lastRow
tbl.Rows.Add
tbl.cell(counter, 1).Range.Text = exWb.Sheets("Tabelle1").Cells(counter, 1)
tbl.cell(counter, 2).Range.Text = exWb.Sheets("Tabelle1").Cells(counter, 2)
tbl.cell(counter, 3).Range.Text = exWb.Sheets("Tabelle1").Cells(counter, 3)
tbl.cell(counter, 4).Range.Text = exWb.Sheets("Tabelle1").Cells(counter, 4)
tbl.cell(counter, 5).Range.Text = exWb.Sheets("Tabelle1").Cells(counter, 5)
tbl.cell(counter, 6).Range.Text = exWb.Sheets("Tabelle1").Cells(counter, 6)
Next counter

End Sub

Private Function GetLastRow(ByVal objExcel As Excel.Application, ByVal exWb As Excel.Workbook) As Integer
Dim lastRow As Integer
lastRow = 0

With exWb.Sheets("Tabelle1")
If objExcel.WorksheetFunction.CountA(.Cells) <> 0 Then
    lastRow = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).row
Else
    lastRow = 1
End If
End With
End Function

For us to answer "why it won't work" you first need to tell us how it didn't work. What happened? — rory.ap
– rory.ap, Commented Jul 10, 2015 at 13:40
Where is it erroring? Have you stepped through the code line by line to see what happens? On what line does it stop doing what you would expect it to do? — Grade 'Eh' Bacon
– Grade 'Eh' Bacon, Commented Jul 10, 2015 at 13:41
I said it didn't do anything (didn't return any error either), as if I didn't press the command button. Sorry if that wasn't clear. — idk123
– idk123, Commented Jul 10, 2015 at 13:42
Please remove On Error Resume Next. It won't show you any errors if that command is in your code — Dorian
– Dorian, Commented Jul 10, 2015 at 13:54

LocEngineer · Accepted Answer · 2015-07-10 13:50:39Z

4

Before End Function you need to do this:

GetLastRow = lastRow

else the function does not return a value.

answered Jul 10, 2015 at 13:50

LocEngineer

2,9271 gold badge18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

idk123 Over a year ago

This did the trick! Thanks so much helping out a vba newbie. One more question - do you maybe know how to successfully quit Excel after everything? I tried putting......Excel.Application.Quit...... before End Sub AND before End Function and they both give me "Run-time error: Automoation error, The message filter indicated that the application is busy."

LocEngineer Over a year ago

Sure. objExcel.Quit is what you need.

idk123 Over a year ago

I thought it didn't work when I tried it before... Now it works great. Thank you so much!

Collectives™ on Stack Overflow

using a function inside a sub (VBA using Word and Excel)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related