Excel VBA Application or Object-Defined Error - Writing Variable Values to Sheets

Question

I am getting an 'Application or Object Defined Error' message in the below function, which is writing variables back to a sheet. I have tried several combinations of Sheets vs Worksheets collections and Range vs Cells objects, but still recieve the same error. The logs confirm that the the issue is coming in the lines beginning with the call to .Range, and all custom objects and methods being utilized have been unit tested. I'm quite confidant that the issue is coming from an incorrect use of the above built-in variables, but I'm struggling to find the correct syntax. Assistance would be much appreciated!

Public Function LoadWorkflowIntoEditor(analysisWorkflowName As String)
'
'Load an existing workflow into the workflow editor in the list view
'

WriteLogs ("LoadWorkflowIntoEditor Called")

Dim tempAnalysisSet As Collection
Set tempAnalysisSet = New Collection

Dim tempKeyAction As AnalysisKeyAction
Set tempKeyAction = New AnalysisKeyAction

With Worksheets(WorkflowSheetName)
    For i = 1 To Master_FlowList.GetWorkflowByName(analysisWorkflowName).NumberOfKeyActions

        WriteLogs ("List Editor Row " & i & "Population Initiated")

        Set tempKeyAction = Master_FlowList.GetWorkflowByName(analysisWorkflowName).GetKeyActionByIndex(i)

        WriteLogs ("Key Action Pulled from Workflow List")

        Set tempAnalysisSet = Master_ModuleList.FindAnalysisSetByKeyActionName(tempKeyAction.AKeyActionName)

        WriteLogs ("Analysis Set pulled from Module List")

        .Range(.Cells(i + ListEditorStartRowNumber_WF, WorkflowColumnNumber_WF)).Value = CurrentWorkflowName

        .Range(.Cells(i + ListEditorStartRowNumber_WF, ModuleColumnNumber_WF)).Value = tempAnalysisSet.Item("Module")
        .Range(.Cells(i + ListEditorStartRowNumber_WF, SystemAreaColumnNumber_WF)).Value = tempAnalysisSet.Item("System Area")
        .Range(.Cells(i + ListEditorStartRowNumber_WF, KeyActionColumnNumber_WF)).Value = tempAnalysisSet.Item("Key Action")

        .Range(.Cells(i + ListEditorStartRowNumber_WF, KeyActionDescriptionColumnNumber_WF)).Value = tempKeyAction.AKeyActionDescription
        .Range(.Cells(i + ListEditorStartRowNumber_WF, KeyActionExpectedResultColumnNumber_WF)).Value = tempKeyAction.AKeyActionExpectedResult
        .Range(.Cells(i + ListEditorStartRowNumber_WF, NextKeyActionColumnNumber_WF)).Value = NextKeyActionListToString(tempKeyAction.GetNextKeyActionList, IPDelimiterCharacter)

    Next i
End With

WriteLogs ("Workflow loaded into list editor")

End Function

Alex

you should just use .cells(xx,xx).value no need to have .Range as .Cells is a range — Sorceri
– Sorceri, Commented Jul 13, 2015 at 21:12
Could it also be that With Worksheets(WorkflowSheetName) is not pointing to the proper sheet hence not defining the object of the .Range? From where are you getting the value of WorkflowSheetName? — nbayly
– nbayly, Commented Jul 13, 2015 at 21:15
@nbayly: The WorkflowSheetName is a global variable which is declared in a Public Module within the Workbook. — Alex Barry
– Alex Barry, Commented Jul 13, 2015 at 21:36
@sorceri: This was my original attempt, and yielded the same error — Alex Barry
– Alex Barry, Commented Jul 13, 2015 at 21:36
@Alex Barry what variable type is WorkflowSheetName and when running the script what value is it populating? — nbayly
– nbayly, Commented Jul 13, 2015 at 21:44

Tim Williams · Accepted Answer · 2015-07-13 21:30:38Z

1

This:

        .Range(.Cells(i + ListEditorStartRowNumber_WF, WorkflowColumnNumber_WF)).Value = CurrentWorkflowName

        .Range(.Cells(i + ListEditorStartRowNumber_WF, ModuleColumnNumber_WF)).Value = tempAnalysisSet.Item("Module")
        .Range(.Cells(i + ListEditorStartRowNumber_WF, SystemAreaColumnNumber_WF)).Value = tempAnalysisSet.Item("System Area")
        .Range(.Cells(i + ListEditorStartRowNumber_WF, KeyActionColumnNumber_WF)).Value = tempAnalysisSet.Item("Key Action")

        .Range(.Cells(i + ListEditorStartRowNumber_WF, KeyActionDescriptionColumnNumber_WF)).Value = tempKeyAction.AKeyActionDescription
        .Range(.Cells(i + ListEditorStartRowNumber_WF, KeyActionExpectedResultColumnNumber_WF)).Value = tempKeyAction.AKeyActionExpectedResult
        .Range(.Cells(i + ListEditorStartRowNumber_WF, NextKeyActionColumnNumber_WF)).Value = NextKeyActionListToString(tempKeyAction.GetNextKeyActionList, IPDelimiterCharacter)

Could be simpler using a With block:

With .Rows(i + ListEditorStartRowNumber_WF)

    .Cells(WorkflowColumnNumber_WF).Value = CurrentWorkflowName    
    .Cells(ModuleColumnNumber_WF).Value = tempAnalysisSet.Item("Module")
    .Cells(SystemAreaColumnNumber_WF).Value = tempAnalysisSet.Item("System Area")
    .Cells(KeyActionColumnNumber_WF).Value = tempAnalysisSet.Item("Key Action")
    .Cells(KeyActionDescriptionColumnNumber_WF).Value = tempKeyAction.AKeyActionDescription
    .Cells(KeyActionExpectedResultColumnNumber_WF).Value = tempKeyAction.AKeyActionExpectedResult
    .Cells(NextKeyActionColumnNumber_WF).Value = NextKeyActionListToString( _
                     tempKeyAction.GetNextKeyActionList, IPDelimiterCharacter)

End With

...and removing the .Range(.Cells(...)) as pointed out by @Sorceri

answered Jul 13, 2015 at 21:30

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

4 Comments

Alex Barry Over a year ago

Tim, I attempted the above (both removing the Range object and using your with block), but am still receiving the same error.

nbayly Over a year ago

Don't you have to define the worksheet that the ranges are locating in? This is what the error is telling me... that the object you are trying to reference is not explicitly defined. Can you run the script and when you get the error, select debug and run your cursor over the script at the break point and see where the error is occurring (not just the row, but which variable). Regards,

Tim Williams Over a year ago

Seems like there's some issue with your variable values - you will need to debug as @nbayll suggests.

Alex Barry Over a year ago

Based on the suggestions of Tim Williams and Nbayly above, I found that the error was caused by a null value variable being passed as an index to the .Cells() call. I found this by setting a breakpoint in the method and setting watches on the global variable names that I was calling. The problem in particular was the global variable KeyActionExpectedResultColumnNumber_WF, which needed to be defined explicitly at a global scope.

Collectives™ on Stack Overflow

Excel VBA Application or Object-Defined Error - Writing Variable Values to Sheets

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related