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
Rungis the issue somehow. It would help if you could post it and/or create a minimal class with ONLY that variable or property.CurrentCell.Value = "'" & thatRung.CommentTextwork ? 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.thisRung.CommentText, so when I executedthisRung.CommentText = thisRung.CommentText & ThatXMLTag.Contentsit was doingnothing&ThatXMLTag.Contents. I would have thoughtnothing& something = something, and when I check my variables it shows something - but if I initializeCommentText = " "in aclass_initializesub in my Rung class, it worksNothinginvolved here: if eitherCommentTextorContentsare 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'sValueproperty.