Range.Find VBA method does not handle the "#" properly

English
,
#1

Hi there. I have a problem with the Range.Find method. For some reason, it works fine in Excel, but not in Calc. I have a sheet with lot of notes taken, including #hashtags. I developed a VBA sub() that retrieves all the lines in my spreadsheet containing a specific hashtag. I made a simple test case for you that captures the essence of the problem. It can be downloaded here

This is the VBA code:

Rem Attribute VBA_ModuleType=VBADocumentModule
Option VBASupport 1
Sub test()
Dim s As String ' String to search
Dim r As Range ' Matching cell

With Worksheets("Sheet1")
    s = .Range("B1").Value
    Set r = .Cells.Find(What:=s, After:=Cells(2, 1), LookIn:=xlValues, LookAt:=xlPart, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
     If not r Is Nothing then .Cells(r.Row, r.Column + 1).Value = "Found here" else .Cells(1, 3).Value = "Not found!"
End With

End Sub

Basically, it takes the value of the B1 cell, and looks for it in the spreadsheet. When found, it displays “Found here” next to the value.

If B1 is set to “#ai”, then it does not find the value on row 4 as expected, but on row 8! The character ‘#’ seems to have a special meaning, like it stood for a digit. See below:
test1

It does not work either when trying to escape the ‘#’ (B1=’\#ai’); the ‘\’ is just taken literally.

So I have no way of making it work. Can someone please help?

Regards.

#2

Hello John and welcome!
I think I understand what your problem is. The point is that code like this will do the job

Option Explicit 

Sub test()
Dim oSheet As Variant
Dim oSearchDescriptor As Variant
Dim oFound As Variant
Dim oCell As Variant
Dim aCellAddress As New com.sun.star.table.CellAddress
	oSheet = ThisComponent.getSheets().getByName("Sheet1")
	oSearchDescriptor = oSheet.createSearchDescriptor()
	oSearchDescriptor.setSearchString(oSheet.getCellRangeByName("B1").getString())
	oFound = oSheet.getColumns().getByIndex(0).findAll(oSearchDescriptor)
	If IsNull(oFound) Then
		oSheet.getCellRangeByName("C1").setString("Not found!")
	Else 
		For Each oCell In oFound.getCells()
			With oCell.getCellAddress()
				oSheet.getCellByPosition(.Column+1, .Row).setString("Found here")
			End With
		Next oCell
	EndIf 
End Sub

image

And the problem is that both VBA and StarBasic are both BASIC. And this leads to the misconception that the code will work in both Excel and Calc. Some similar work can be done with an electric drill and a chainsaw. But everything else is simply impossible. If you want your code to work well with Calc, then try to forget VBA (at least for a while).

1 Like
#3

Thank you very much for your swift reply. That’s very much appreciated.

Your solution works well. My concern is that I now have to translate all my code to StarBasic, on which I know virtually nothing. Apparently it has a very different object structure and viewing the code you’ve posted, I also get the impression it’s more complex. There are a lot of calls to methods whereas in VBA you would simply access readily available attributes. It’s a bit of a learning curve!

It’s a pity that the Range.Find() method does not work as expected. I suspect it is a bug because the behavior when using a ‘#’ is not documented and not consistent with VBA. Maybe that’s something the development team would want to look at.

#4

I don’t think this is a mistake. More precisely, an “obvious mistake”. MS has always been famous for incomplete documentation (nothing personal, it’s just business: “Do you want to know more about our products than we wrote in the Help? Sign up for paid courses!”).

image
image955×693 72 KB

Adapting own product to another poorly documented product is backbreaking work.

#5

… but please don’t hesitate to file anything you consider a bug, to the bug tracker. We all know how difficult / impossible full compatibility is; but specific improvements may turn out to be simple - they just need analysis in the dedicated place. Thanks!

1 Like
#6

You are not alone in this. I don’t think there are many people who know this (Excel) method in detail. :slightly_smiling_face:

The escape symbol for this method is ~. In your example, when searching for ~#ai text, Excel and Calc produce the same results.
The problem, as already noted, is that in Excel methods, wildcard characters are understood as * and ?. Probably (I didn’t look at the text), when implementing the method in Range.Find in Calc, the developers expanded the list of wildcard characters.

I’ll make a bug and report it here.

#7

Thank you! This is all I need for now.

#8

Yes, it’s better to have some consistency between Basic and VBA.

#9

tdf#158185

1 Like
#11

Thanks to the team. They’re doing a great work. :clap:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.