Ask Your Question
0

Result of macro not displaying in Cell but does display in Function Wizard

asked 2016-10-15 13:57:50 +0200

evileyeball gravatar image

Hello Libre Office.

I'm having a bit of a fun time here. This is my first foray into Macros in Libreoffice I found a page online with a macro someone had built to find House words data of of a wiki page about a song of Ice and Fire and have modified the code they had for my attempt at fetching Magic The Gathering Prices

I have the following function

Public Function FETCHPRICE(sSet as String, sCard as String) as string


   sURL = "http://mtggoldfish.com/index/" & sSet & "#paper"
   dim sWords as String
   oSimpleFileAccess = createUNOService ("com.sun.star.ucb.SimpleFileAccess")
   oInpDataStream = createUNOService ("com.sun.star.io.TextInputStream")
   on error goto NoCardFound
   oInpDataStream.setInputStream(oSimpleFileAccess.openFileRead(sUrl))
   on error goto NoCardFound
   dim delimiters() as long
   sContent = oInpDataStream.readString(delimiters(), false)

   lStartPos = instr(1, sContent, "" )
   if lStartPos = 0 then
     Msgbox("Table Not found")
     FETCHPRICE = "-1.0"
     exit function
   end if   
   lEndPos = instr(lStartPos, sContent, "
") sTable = mid(sContent, lStartPos, lEndPos-lStartPos + 8) lStartPos = instr(1, sTable, sCard ) if lStartPos = 0 then Msgbox("Card " & sCard & " Not Found for Set " & sSet) FETCHPRICE = "-1.0" exit function end if lEndPos = instr(lStartPos, sTable, "") sRow = mid(sTable, lStartPos, lEndPos-lStartPos + 5) oTextSearch = CreateUnoService("com.sun.star.util.TextSearch") oOptions = CreateUnoStruct("com.sun.star.util.SearchOptions") oOptions.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP oOptions.searchString = "" oTextSearch.setOptions(oOptions) oFound = oTextSearch.searchForward(sRow, 0, Len(sRow)) If oFound.subRegExpressions = 0 then FETCHPRICE = "-1.0" exit function end if lStartPos = oFound.endOffset(0) + 1 lEndPos = instr(lStartPos, sRow, "") sPrice = mid(sRow, lStartPos, lEndPos-lStartPos) FETCHPRICE = sPrice exit function NoCardFound: Msgbox "Error Finding Card" FETCHPRICE = "-1.0" End Function

When I run it with Known values Like =FETCHPRICE("RAV","Circu, Dimir Lobotomist") it either Errors (which I assume is a result of me hitting thier page too frequently and in future revisions I can correct for) or it Gives me back a BLANK CELL I've inserted Breakpoints and put a watch on the sPrice Variable which is indeed a string variable with the correct price value in it when it is returns

Now the strange part is

When I open the Fuction wizard and enter the function it shows in the result window the correct resulting price. Click OK in there to make the formula insert into the cell and it Either gives me "Error Finding Card" or gives me a Blank cell

I tried several things to remedy this such as Returning "" & sPrice

Changing the function so it returns Double (and changing the "-1.0" returns to -1.0 But that didn't work because when I tried to cDbl(sPrice) it didn't like it It said Invalid Procedure call I saw that Cdbl to go from String to Double needs to be cDbl("12345") format so I tried making it cdbl("""" & sPrice & """")

to concatinate some "'s around it But no dice

I tried setting sPrice to "Price is: " & sPrice on the line before I returned it but no dice I ... (more)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-10-15 16:53:46 +0200

evileyeball gravatar image

I figured out what the problem was. What I thought was "2.30" was really "chr(10)2.30chr(10)" stripping off the first and last character of the string which were newline characters made it display properly.

I've changed the code now so it returns double values and the -1.0 values are getting changed to be different so I can tell where it errored (as a card's price should never be negative negative values make for a good error code)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-10-15 13:57:50 +0200

Seen: 114 times

Last updated: Oct 15 '16