0

OK, I've seen a lot of similar questions on here, but in all the ones I found, either there wasn't an answer, or it wasn't the same as my situation and didn't apply, or I tried the solution and it didn't work for me.

Issue is, in my code snippet below, I am getting "Run-time error '1004': Application-defined or object-defined error assigning Range.Value Property"

I do have Option Explicit set.

R, NR, and RungTopRow are all declared as integer types earlier in the code. CommentRow is also an integer (only ever equal to 0 or 1, declared as an integer rather than Boolean because I use the 0 or 1 as a multiplier in equations). CurrentCell is declared as type Range. LadderSheet is of type Worksheet set to a specific worksheet in my Excel file. I have used LadderSheet numerous other times in my program without any issues. RowSize is a defined constant equal to 6. thatRung is a class variable of type Rung that I have defined. CommentText is a public variable that is declared as part of the Rung Class. I have tried declaring CommentText as a string and defining it as a variant. I get the same error either way. Option Explicit is also set in the Rung Class Module.

NR is the number of Rungs in a ladder logic source that I'm using. This is only part of my code, additionally I have code to exit the program if RungTopRow ever equals or exceeds the maximum number of rows allowed by excel by comparing to a defined Const LastXLRow = 1048576.

Regardless, when I insert a breakpoint, I find that I'm getting the error on a value of RungTopRow = 3. So, I'm definitely not exceeding the maximum row.

Everything I've bundled into {Other Code Here} can run without error when I comment the below section out. Actually everything runs otherwise as expected if I comment out the .value assignment statement.

    For R = 0 To NR
        
    If R = 0 And CommentRow Then
        With LadderSheet.Range("C" & CStr(RungTopRow + 1) & ":P" & CStr(RungTopRow + RowSize - 1))
            .MergeCells = True
            .ShrinkToFit = False
            .WrapText = True
        End With
         Set CurrentCell = LadderSheet.Cells(RungTopRow + 1, 3)
        'this line keeps giving an application defined error
        CurrentCell.Value = thatRung.CommentText

    End If

    ... {Other Code Here} ...

    Next R

First I tried

LadderSheet.Cells(RungTopRow + 1, 3).Value = thatRung.CommentText

I replaced LadderSheet.Cells(RungTopRow, 3) with CurrentCell as that worked for me in another similar situation, but for some reason isn't working here.

I have also tried

currentComment = thatRung.CommentText
currentCell.Value = currentComment

With currentComment and thatRung.CommentText declared as strings. I get the same error, triggering on the assignment statement of currentCell.Value , so it is not due to the definition of thatRung.CommentText

Note that if I directly assign it to a literal string as follows:

CurrentCell.Value = "hello"

or as follows:

currentComment = "hello"
currentCell.Value = currentComment

It executes without issue. Of course, I don't get the text I actually want in that case. I need the text stored in thatRung.CommentText to go into the cell defined by the range CurrentCell.

Update The Rung Class is too big to post here, but the only place where CommentText is mentioned is in the delcarations as follows:

Option Explicit
'These values are directly defined
Public Number As Integer
Public NumBranches As Integer
Public RungType As String
Public CommentText As String 'Neither string nor variant working
Public RungText As String

'These values will need to be calculated
Private Branches() As Branch
Private Items() As Item
Private ItemsEmpty As Boolean
Private CommandsEmpty As Boolean
Private InputsEmpty As Boolean
Private OutputsEmpty As Boolean
Private BranchesEmpty As Boolean

Const maxItemsperRow = 4 'Make sure other modules have same value in constant

Perhaps also relevant are places where CommentText is manipulated prior to the code I posted above in the following line in my main module code (ImportL5x):

thisRung.CommentText = thisRung.CommentText & ThatXMLTag.Contents

The code I posted is in a subroutine called DrawRung and thisRung is passed to DrawRung as an argument:

DrawRung thisRung

And DrawRung is defined with thatRung:

Public Sub DrawRung(thatRung As Rung)

ThatXMLTag is another custom class I defined called XMLTag. Contents is also a string.

This is all of the code for the XMLTag class (it is only declarations right now, everything is assigned externally in another module):

Option Explicit

Public Enum XMLType
   XMLStart = 0 'No starting /, no ! data
   XMLContent = 1 'Starting !
   XMLStop = 2 'Starting /
   XMLUnknown = 3 'Anything else
End Enum
Public Name As String
Public Contents As String
Public TagType As XMLType
19
  • 2
    It does sound like your custom class Rung is the issue somehow. It would help if you could post it and/or create a minimal class with ONLY that variable or property. Commented Apr 28, 2021 at 20:42
  • 1
    What's the value of thatrung.commentText when you get the error? Commented Apr 28, 2021 at 21:03
  • 1
    Does CurrentCell.Value = "'" & thatRung.CommentText work ? If what you're assigning looks like a formula then Excel might choke on it. You can also try formatting the cell as Text before assigning the value. Commented Apr 28, 2021 at 22:09
  • 1
    @TimWilliams thank you for the suggestion. It does work when I add "'"... but that, of course doesn't give me what I want. But thanks to your suggestion I figured it out. If I add ANYTHING to it, it works. I realize now I never initialized thisRung.CommentText, so when I executed thisRung.CommentText = thisRung.CommentText & ThatXMLTag.Contents it was doing nothing & ThatXMLTag.Contents. I would have thought nothing & something = something, and when I check my variables it shows something - but if I initialize CommentText = " " in a class_initialize sub in my Rung class, it works Commented Apr 28, 2021 at 22:25
  • 2
    There is no Nothing involved here: if either CommentText or Contents are not initialized they would just be empty strings. There are no issues related to concatenating empty strings. Your real issue is likely that Excel thinks you're trying to assign a (broken) formula to the cell's Value property. Commented Apr 28, 2021 at 22:40

1 Answer 1

1

When assigning something to an Excel cell - even when explicitly using the Value and not the Formula/FormulaR1C1/etc property - Excel will attempt to parse the cell content as a formula if it "looks" like it might be a formula.

For example, if the cell content begins with "=" Excel will try to parse the content as a formula, and if that fails will generate the type of run-time error you're describing.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.