Showing Object Required and sub and function not defined Powerpoint VBA

Question

Hi im trying to read excel file into Powerpoint VBA. but i am getting error object required and sub and function not defined

one line is working and while executing group code showing error

Public pName(), pMat(), pGrade(), pHDT(), pTemp(), planeName(), ledName() As Variant
Public pCount, planeCount, ledCount As Long


Sub test()
Dim xlApp As Object
Dim xlWorkBook As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set xlWorkBook = xlApp.Workbooks.Open(ActivePresentation.Path & "\PPT_INPUT.xlsm", True, False)
xlWorkBook.sheets(1).Range("A8").Value = "Hello" ' **This Line working**


'------------------------------------------ **this section not working**
pCount = ActiveSheet.Cells(5, Columns.Count).End(xlToLeft).Column
ReDim pName(pCount - 1), pMat(pCount - 1), pGrade(pCount - 1), pHDT(pCount - 1), pTemp(pCount - 1)
For i = 1 To pCount
    pName(i - 1) = Cells(5, i).Value
    pMat(i - 1) = Cells(6, i).Value
    pGrade(i - 1) = Cells(7, i).Value
    pHDT(i - 1) = Cells(8, i).Value
    pTemp(i - 1) = Cells(9, i).Value
Next
'------------------------------------------------


xlWorkBook.Close savechanges:=False
xlApp.Quit
Set xlApp = Nothing
Set xlWorkBook = Nothing
End Sub

Answer 1

ActiveSheet and Cells are Excel specific. When you run VBA code in Excel and write ActiveSheet, it will be translated implicitly to Application.ActiveSheet. Similar, using Cells without specifying any worksheet, it will be translated implicitly to Application.ActiveSheet.Cells. The application object is of type Excel.Application

In Powerpoint, this cannot work. There is an Application-object in Powerpoint-VBA, but that holds PowerPoint-specific properties and methods (type is Powerpoint.Application). However, you are already using an Excel.Application-object, you assigned it to the variable xlApp.

I would suggest to assign the worksheet you want to work with to an Excel.Worksheet Variable (you already declared one but you are not using it). I would also suggest you use Early Binding, just add a reference to the Microsoft Excel Library. It helps you writing your code because Intellisence can show you valid methods and properties - if you use Object it cannot help you with that.

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Dim xlWorkBook As Excel.Workbook   
Set xlWorkBook = xlApp.Workbooks.Open(ActivePresentation.Path & "\PPT_INPUT.xlsm", True, False)

Dim xlSheet As Excel.Worksheet
Set xlSheet = xlWorkBook.sheets(1)
' Or, if you insist on ActiveSheet 
' (but honestly, you shouldn't, always specify a sheet explicitly in Excel)
' Set xlSheet = xlApp.ActiveSheet

' Now, work with the variable. Note the `.` in front of `.Range` and `.Cells`
With xlSheet  
    .Range("A8").Value = "Hello"
    pName(i - 1) = .Cells(5, i).Value
    (...)
End With

One extra remark: If you write Public pCount, planeCount, ledCount As Long, only ledCount is defined as Long, all other variables are defined as Variant. In VBA, you need to specify the type to every variable explicitly:

Public pCount As Long, planeCount As Long, ledCount As Long

Update With late binding, you simply need to change the variable declaration. Advantage: You don't need a reference. Disadvantage: No Intellisence, the compile can catch errors like mistyping a method or a property only at runtime, not at compile time

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Dim xlWorkBook As Object
Set xlWorkBook = xlApp.Workbooks.Open(ActivePresentation.Path & "\PPT_INPUT.xlsm", True, False)

Dim xlSheet As Object
Set xlSheet = xlWorkBook.sheets(1)

With xlSheet  
    .Range("A8").Value = "Hello"
    pName(i - 1) = .Cells(5, i).Value
    (...)
End With