Dear all,
I’m trying to check if a specific cell of a specific sheet in LO calc is equal to a given string.
Can anybody please explain why this code will not work?
If ThisComponent.Sheets("sheet_name").getCellRangeByName("B3").String = "my_string" Then
exit sub
End If
Thanks!
Why did you decide it wouldn’t work?
@Zizi64
the shortened expression …Sheets("sheet_name") instead …Sheets.getByName("sheet_name") is syntactical correct! not correct
Please upload the full macro code and a sample file here.
@karolus,
Well, it’s not that simple. Simple test
Sub test
If ThisComponent.Sheets("sheet_name").getCellRangeByName("B3").String = "my_string" Then
Print "my_string"
Else
Print "not my_string"
End If
End Sub
will show
for empty B3 and will show
if B3 contain string my_string
The problem is that we will get this message even if there is no sheet named “sheet_name” in the current spreadsheet… Perhaps this is what was asked in the question?
Using
Sheets.getByName("sheet_name")
did solve the issue!
Thanks!
@JohnSUN :Therefore I wrote “syntactically correct” … no one except the OP can know if the sheet with Name sheet_name exists!
No, you misunderstood my hint.
Sheets(something) expects a numeric sheet index. Yes, the translator doesn’t mind having a text string as a parameter (even though it expects an integer).
The translator does not argue with the programmer - what if you use a string that can be interpreted as a number? Something like Sheets(“1”)? Therefore, the parameter simply tries to convert the string to a value and, as in this case, calls Sheets(0) - and there is always a zero sheet in the spreadsheet - so no error occurs.
Tibor immediately gave the correct answer - “write the code as expected and you will not have problems”
I went the other way. “A detailed question contains half of the correct answer.” This question was not detailed and I was trying to push the OP into a more detailed statement of the problem…
OMG youre right…I would not have expected THIS behavior! BASIC is really garbage!
Oh, you have not yet seen what disgusting garbage some people write with it … 
In fact, it’s not about the tool, but in whose hands it is. If the programmer has figured out the intricacies of using the tool, then no one can stop him from using it to solve problems. (I know a guy who for everything uses VBA in Access 2003 - and he’s pretty good at it!)
The way I had put the facts into a comment before I found other comments to the same effect: I post it now nevertheless.
@JohnSUN knew of course, but visitors may not.
ThisComponent.Sheets("sheet_name) expects a number of type Long in the argument position. Any string found there will evaluate to 0 then due to funny concepts concerning automatic conversion. And if by accident the wrongly chosen sheet has “my_string” in B3, the effect will be an undetected error.
This isn’t just about Basic. Automatic conversion was originally made for lazy users disregarding that lazy people not always are the most clever ones.
What would you (anybody here) expect to happen if somebody not having understood overloading (functions, operators) writes programmes in C++?
Syntactic sugar may act as a dangerous poison.
@Lupp
but this is exactly about basic, which leads to hidden errors in this constellation… and not about deriving an inadmissible correlation between intelligence and diligence of people!