By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Error: Object variable or Block variable not set

P: 41
Thanks to all the people who reply so promptly to my questions! I have another one related to the form I am working on. When I try to call this function from another, I get the "Object variable or Block variable not set" error... but when I paste it as a macro in Excel I have no problem... could anyone please help me explain this?

The error happens at the Cells.Find line - starting on line 7. Thanks in advance.

Expand|Select|Wrap|Line Numbers
  1. Function Finder(DateSelect As String) As Integer
  2.     Dim egg As String
  3.     Dim colval As Integer
  4.  
  5.     egg = "AmtU_" & DateSelect
  6.  
  7.     Cells.Find(What:=egg, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
  8.            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
  9.            .Activate
  10.     Finder = ActiveCell.Column
  11.     Exit Function
  12. End Function
Sep 10 '07 #1
Share this Question
Share on Google+
8 Replies


FishVal
Expert 2.5K+
P: 2,653
Thanks to all the people who reply so promptly to my questions! I have another one related to the form I am working on. When I try to call this function from another, I get the "Object variable or Block variable not set" error... but when I paste it as a macro in Excel I have no problem... could anyone please help me explain this?

The error happens at the Cells.Find line - starting on line 7. Thanks in advance.

Expand|Select|Wrap|Line Numbers
  1. Function Finder(DateSelect As String) As Integer
  2.     Dim egg As String
  3.     Dim colval As Integer
  4.  
  5.     egg = "AmtU_" & DateSelect
  6.  
  7.     Cells.Find(What:=egg, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
  8.            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
  9.            .Activate
  10.     Finder = ActiveCell.Column
  11.     Exit Function
  12. End Function
Hi, cloh.

Keep in my mind that the code is not in Excel. ;)
Properties like "Cells", "ActiveCell" available in Excel module without referencing as long as they are the properties of Excel.Application class.
In Access module you need to reference them through Excel.Application object obtained on excel opening via CreateObject or GetObject.
Sep 10 '07 #2

P: 41
Hi FishVal,

You're right of course. Thanks for being patient with this newbie :-)

I tried to reference the properties using the Excel.Application object like you said... I still have a problem with the "Cells" property. My code is as shown below. All I want is to search for the string egg in the first row of each worksheet.

Expand|Select|Wrap|Line Numbers
  1. Function Finder(DateSelect As String) As Integer
  2.     Dim egg As String
  3.     Dim colval As Integer
  4.     Dim xlapp As excel.Application
  5.     Dim xlbook As excel.Workbook
  6.     Dim xlsheet As excel.Worksheet
  7.     Dim xlrange As excel.range
  8.     Dim ExcelObject As Object
  9.  
  10.     Set xlapp = CreateObject("Excel.Application")
  11.     Set xlbook = xlapp.Workbooks.Add
  12.     Set xlsheet = xlbook.ActiveSheet
  13.  
  14.     xlsheet.range("A1").Select        
  15.     egg = "AmtU_" & DateSelect
  16.     xlsheet.Cells.Find(What:=egg, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
  17.            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
  18.            .Activate
  19.     Finder = ActiveCell.Column
  20.     Exit Function
  21. End Function
Sep 10 '07 #3

P: 41
actually nevermind! as soon as i took away the "xlsheet" from line 16 my code worked... but if you can explain it to me, then at least i'll learn something! :-)
Sep 10 '07 #4

FishVal
Expert 2.5K+
P: 2,653
actually nevermind! as soon as i took away the "xlsheet" from line 16 my code worked... but if you can explain it to me, then at least i'll learn something! :-)
:)
Actually I don't know what to explain.
I really don't understand why it works without reference to Worksheet object.
The referencing problem I still see in the code is implicit call of ActiveCell property in line #16, #19.
Sep 10 '07 #5

P: 41
You're right, FishVal... the problem still exists... this is the code as it stands and the same error still appears. I'm not quite sure why...

Expand|Select|Wrap|Line Numbers
  1. Function Finder(DateSelect As String) As Integer
  2.     Dim egg As String
  3.     Dim colval As Integer
  4.     Dim xlapp As excel.Application
  5.     Dim xlbook As excel.Workbook
  6.     Dim xlsheet As excel.Worksheet
  7.     Dim xlrange As excel.range
  8.     'Dim xlcell As
  9.     Dim ExcelObject As Object
  10.  
  11.     Set xlapp = GetObject(, "Excel.Application")
  12.     Set xlbook = xlapp.Workbooks.Add
  13.     Set xlsheet = xlbook.ActiveSheet
  14.  
  15.     xlsheet.range("A1").Select
  16.  
  17.     egg = "AmtU_" & DateSelect
  18.     'MsgBox "this is egg " & egg
  19.  
  20.     Cells.Find(What:=egg, LookIn:=xlValues, LookAt:= _
  21.            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
  22.            .Activate
  23.     Finder = ActiveCell.Column
  24.  
  25.     Exit Function
  26. End Function
Sep 11 '07 #6

FishVal
Expert 2.5K+
P: 2,653
You're right, FishVal... the problem still exists... this is the code as it stands and the same error still appears. I'm not quite sure why...

Expand|Select|Wrap|Line Numbers
  1. Function Finder(DateSelect As String) As Integer
  2.     Dim egg As String
  3.     Dim colval As Integer
  4.     Dim xlapp As excel.Application
  5.     Dim xlbook As excel.Workbook
  6.     Dim xlsheet As excel.Worksheet
  7.     Dim xlrange As excel.range
  8.     'Dim xlcell As
  9.     Dim ExcelObject As Object
  10.  
  11.     Set xlapp = GetObject(, "Excel.Application")
  12.     Set xlbook = xlapp.Workbooks.Add
  13.     Set xlsheet = xlbook.ActiveSheet
  14.  
  15.     xlsheet.range("A1").Select
  16.  
  17.     egg = "AmtU_" & DateSelect
  18.     'MsgBox "this is egg " & egg
  19.  
  20.     Cells.Find(What:=egg, LookIn:=xlValues, LookAt:= _
  21.            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
  22.            .Activate
  23.     Finder = ActiveCell.Column
  24.  
  25.     Exit Function
  26. End Function
Hi, cloh.

Finally I've got a time to look close to your code.
  • code
    Expand|Select|Wrap|Line Numbers
    1.  Cells.Find(What:=egg, LookIn:=xlValues, LookAt:= _
    2.            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
    3.            .Activate
    will fail when no cell will be found. Really you try to invoke Activate method for Range object expected to be returned by Cells.Find method. If no cell found then object is set to Nothing and Nothing.Activate will raise error you are getting.
  • you use ActiveCell to determine the result of search but some cell will always be active though not corresponding to search criteria

Here is a sample code for searching within worksheet.
Expand|Select|Wrap|Line Numbers
  1. Public Function Finder(DateSelect As Integer) As Integer
  2.  
  3.     Dim egg As String
  4.     Dim colval As Integer
  5.     Dim xlapp As Excel.Application
  6.     Dim xlbook As Excel.Workbook
  7.     Dim xlsheet As Excel.Worksheet
  8.     Dim xlrange As Excel.Range
  9.  
  10.     Set xlapp = CreateObject("Excel.Application")
  11.     With xlapp
  12.         .Visible = True
  13.         'leave Excel app running when the reference (xlapp) destroyed
  14.         .UserControl = True
  15.         Set xlbook = .Workbooks.Add
  16.         Set xlsheet = xlbook.ActiveSheet
  17.     End With
  18.  
  19.     egg = "AmtU_" & DateSelect
  20.     'run find method for the whole worksheet
  21.     'and set range object to cell found
  22.     Set xlrange = xlsheet.Cells.Find(egg)
  23.     If xlrange Is Nothing Then
  24.         'nothing found
  25.         Finder = -1
  26.     Else
  27.         'cell found
  28.         Finder = xlrange.Row
  29.     End If
  30.  
  31.  
  32.     'destroy objects, Excel application stays running
  33.     'bkz xlapp.UserControl = True
  34.     Set xlrange = Nothing
  35.     Set xlsheet = Nothing
  36.     Set xlbook = Nothing
  37.     Set xlapp = Nothing
  38.  
  39. End Function
  40.  
Sep 11 '07 #7

P: 41
FishVal, what would i do without you? I used the sample code as a template and modified my code to match what I wanted and it now works! I've attached it at the bottom in case anyone has a similar problem. Thank you SO much!

Expand|Select|Wrap|Line Numbers
  1. Function Finder(DateSelect As String, sheetindex As Integer) As Integer
  2.     Dim egg As String
  3.     Dim colval As Integer
  4.     Dim xlapp As excel.Application
  5.     Dim xlbook As excel.Workbook
  6.     Dim xlsheet As excel.Worksheet
  7.     Dim xlrange As excel.range
  8.     Dim sheetname As String
  9.     sheetname = "LocID" & sheetindex
  10.     DateSelect_Ref = Format(DateSelect, "MM/DD/YYYY")
  11.  
  12.     Set xlapp = GetObject(, "Excel.Application")
  13.     Set xlbook = ActiveWorkbook
  14.     Set xlsheet = xlbook.Worksheets(sheetindex)
  15.  
  16.     xlsheet.Activate
  17.     Dim title As String
  18.  
  19.     egg = "AmtU_" & DateSelect_Ref
  20.     Set xlrange = xlsheet.Cells.Find(egg)
  21.     If xlrange Is Nothing Then
  22.         Finder = -1
  23.     Else
  24.         Finder = xlrange.Column
  25.     End If
  26.  
  27.     Set xlrange = Nothing
  28.     Set xlsheet = Nothing
  29.     Set xlbook = Nothing
  30. End Function
Sep 12 '07 #8

FishVal
Expert 2.5K+
P: 2,653
Hi, cloh.

I'm glad you appreciate my help and thanks for posting successful solution for those that visit the thread later.

You are welcome.
Good luck and happy coding. :)
Sep 12 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.