0

I have opened a new workbook as an Object in the first Sub and need to make use of the opened workbook to do another things in another Sub, can I do that? But I have encountered error: ''Run Time error 424, object required'' as script sample as below, how can I use the opened workbook in another Sub?

Sub test1()

Dim wb as Object
Dim Spreadsheet1 as Object
Set wb = CreatObject ("excel.Appplication")
wb.visiable= true
Set wb = wb.workbooks.Add
Set Spreadsheet1 = wb.Sheets("Sheet1")
Call test2
End Sub

Sub test2()
Spreadsheet1 .Cells(1,1) = "Do Another things"
End Sub
Community
  • 1
  • 1
GoodToBe1990
  • 29
  • 7
  • 1
    Remove the space between `Spreadsheet1` and `.Cells(1,1)`? Fix the typo in `visiable`? Use `Option Explicit`? – GSerg Jul 15 '17 at 14:23
  • 3
    Pass `Spreadsheet1` as a parameter to `test2` or declare it as a [global variable](https://stackoverflow.com/q/2722146/11683). – GSerg Jul 15 '17 at 14:28
  • 1
    And don't reuse the `wb` object for both the Excel Application object, and as a Workbook object referring to a workbook opened within that Application object. I'm not sure whether it works or not (you will certainly lose the ability to refer to the Application again) but it is definitely confusing to any future readers of your code. – YowE3K Jul 15 '17 at 18:55
  • What language is this intended for - it **may** affect the answers. All we can tell from the question is that it **probably** isn't intended for Excel, because of your need to create an Excel Application, so is it Word VBA, or Access VBA, or Powerpoint VBA, or Outlook VBA, or something else that isn't even a Office application coding language? – YowE3K Jul 15 '17 at 18:59

0 Answers0