How to pass sheet as an argument in Powerpoint VBA?

Question

I have been trying to work on powerpoint that has an excel database in background. For now I am having trouble passing sheets as arguement in PPT VBA. The function lastrow and lastcoulmn return an error that "user-defined type not defined". Help would be appreciated. thanks.

Dim oXLApp As Object
Dim oWb As Object
Dim Deps As Excel.Range
Dim Dep, Shift, Name, EmpNo, Sup As String
Dim Sups As Excel.Range
Dim Shifts As Excel.Range

Public Sub getexceldata()
Dim str As String
Set oXLApp = CreateObject("Excel.Application")
Set oWb = oXLApp.Workbooks.Open(ActivePresentation.Path & "\" & "Property Wide.xlsm")

'Shifts
Set Shifts = oWb.Sheets(4).Range("A1:A" & lastRow(oWb.Sheets(4), "a"))

'departments
Set Deps = oWb.Sheets(3).Range("A1:" & Chr(lastColumn(oWb.Sheets(3), "1") + 64) & "1")

'supervisors

End Sub


Public Function lastRow(ByVal SheetA As Excel.Application.Sheet, Optional Columnno As Char = "/")     As Long
If (Columnno = "/") Then
Set lastRow = SheetA.UsedRange.Row - 1 + SheetA.UsedRange.Rows.Count
Else
Set lastRow = SheetA.Range(Columno & Rows.Count).End(xlUp).Row
End If
End Function

Public Function lastColumn(ByVal SheetA As Excel.Application.Sheet, Optional rowno As Char = "/")     As Integer
If (rowno = "/") Then
Set lastColumn = SheetA.UsedRange.Column - 1 + SheetA.UsedRange.Columns.Count
Else
Set lastColumn = SheetA.Cells(rowno, Columns.Count).End(xlToLeft).Column
End If
End Function

Answer 1

The first issue is that CHAR is not a valid variable type so I would suggest changing this to string.

Next make sure to include the Microsoft Office Excel 14.0 Object Library in your code reference.

With that you can make some slight adjustment to your code an everything should work.

Dim oXLApp As Object
Dim oWb As Object
Dim Deps As Excel.Range
Dim Dep, Shift, Name, EmpNo, Sup As String
Dim Sups As Excel.Range
Dim Shifts As Excel.Range

Public Sub getexceldata()
Dim str As String
Set oXLApp = CreateObject("Excel.Application")
Set oWb = oXLApp.Workbooks.Open(ActivePresentation.Path & "\" & "Property Wide.xlsm")

'Shifts
Set Shifts = oWb.Sheets(4).Range("A1:A" & lastRow(oWb.Sheets(4), "a"))

'departments
Set Deps = oWb.Sheets(3).Range("A1:" & Chr(lastColumn(oWb.Sheets(3), "1") + 64) & "1")

'supervisors

End Sub


Public Function lastRow(ByVal SheetA As Worksheet, Optional Columnno As String = "/")     As Long
If (Columnno = "/") Then
Set lastRow = SheetA.UsedRange.Row - 1 + SheetA.UsedRange.Rows.Count
Else
Set lastRow = SheetA.Range(Columno & Rows.Count).End(xlUp).Row
End If
End Function

Public Function lastColumn(ByVal SheetA As Worksheet, Optional rowno As String = "/")     As Integer
If (rowno = "/") Then
Set lastColumn = SheetA.UsedRange.Column - 1 + SheetA.UsedRange.Columns.Count
Else
Set lastColumn = SheetA.Cells(rowno, Columns.Count).End(xlToLeft).Column
End If
End Function

With that you should have what you need.