Changing the contents of any cell from a function

[FloridaDude]

I wrote a function and created a dialog for the simple task of browsing and retrieving file names of pictures for our products. What I would like to do is store the file name in an adjacent column so that my function can check it to see if the file name has been found already. That way, I don't have to browse for the files every time I open the document. Cell contents can be easily changed inside a macro with something like the following:

Code: Select all

Sub MacroEdit

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
 
Doc = StarDesktop.CurrentComponent
Sheet = Doc.Sheets(1)
Cell = Sheet.getCellByPosition(1,1)

Cell.setString("this cell was edited by a macro")
      
End Sub

I can't find any equivalent way to do this from inside a function. It appears that changing cell contents IS allowed from macros but IS NOT allowed from functions. Is there any trick to it?

[bobban]

Why do you need to write this in a Function?

This pretty much sums it up:

Do not use any object references in userdefined functions. Spreadsheet functions, most of the built-in ones and the ones in add-ins, take (arrays of) values and return (arrays of) values.

I don't think functions have access to the calling object or document, for reasons I cannot explain.

[FloridaDude]

I don't think functions have access to the calling object or document, for reasons I cannot explain.

That's what I thought.

I want to access the spreadsheet from a function so that a single function call can store outputs in more than one cell. My function takes an argument (the name of a product) and calls up a dialog that says "Browse for the picture of: <product name>". Then, you press the browse button which opens a "file open" dialog". After the pic is selected and the "OK" button is pressed, the file name is output to the cell in which the function was called. I can autofill the function down a column of a spread sheet that has all our products, prices, etc. and then quickly fetch all the file names for the product pics. It puts everything into a nice format that I can upload to our web site. The problem is, every time I open the spreadsheet I have to re-browse for all the pics. There's a few hundred of them. If I could write the file name to a second column then my function could check that column to see if the file name has already been found.

I could do it all as a loop in a macro but I would have to continually edit the macro as the product spreadsheet is modified. I guess I'll have to do it that way.

[bobban]

I could do it all as a loop in a macro but I would have to continually edit the macro as the product spreadsheet is modified. I guess I'll have to do it that way.

Why would you have to continually modify the macro?

As I understand it, you have a column named 'Product Name', and you keep adding more rows I guess as products get added (or removed). Your macro can simply loop down that column, outputting the results to another column. Say you currently have 500 products, that doesn't mean the macro can't loops over say 1000 cells in that column to accommodate future growth. The only hitch will be that you must run the macro to refresh the page (you can easily bind this to an embedded button on the page), although obviously you would want it to check the 'output cell' so it wasn't redoing the search for a product's photo that was already handled.