How do i change Cells within functions?

[Bommel]

i want to change a cell within a function. here is a sample function:

public function test(result as variant) as variant
Cells(1, 2).value = "whatever text i want"
test = result
end function

when i have this function and then write the following formula into a cell,
it does not work: =test("justsometext")

information why i want functions to change cells, and not just some
usercontrolls: I want to show all informations from all lists i have on a
single sheet, so when i print it i only need to print only 1 page where all
my informations i need summarized. problem is: the lenght of the lists and
thus the space they take on that page vary very much. in excel i can only
give them a fixed amount of space usually, so i would need about 3 pages for
all lists instead of 1. thats where my functions would come in: i would
create a function for every list i have, that fills the cells below it with a
list, notice where the list's end is and then place the next function (and
thus the next list) after the last entry of it own list.

 

[Frank Kabel]

Hi
as already answered to your previous post: This simply is
not possible. Functions invoked from a cell CAN'T change
other cells (or formats). Excel restricts these functions
to only return values to the cell they reside in.

For your question: Why not use 'Data - Filter' to filter
the list prior to printing it?

 

[Paul Lautman]

Have you tried creating your function as an array function?

 

[Bommel]

Thanks for your help frank. it helped alot cuz now i know that i have to do
the complicated solution to my problem (i will create a button that calls a
function1 that creates my list and calls next function2 and tells that
function2 where to start with its list. function2 then will print its list
and call next function3 and so on.....)
to your question: "Why not use 'Data - Filter' to filter the list prior to
printing it?" :i can not use autofilter because it hides whole rows. that is
inacceptable for me. excample: i got a list of Partymembers on left side of
my sheet and list of partyaccessoires in middle and list of what partyfood i
got stored on right side of my sheet. below of all i got a list with many
columns but few rows that shows who lent me what number of available
musicCD's in what music-direction and so on.
if i now would do autofilter on partymembers (to not show those that wont
come this time) it would also hide me some accessoires and partyfood. if i
don't filter in any way, partymembers alone would take about 3 pages...
usually i got enough space on one sheet of paper (din A4) if i am writing
everything with my pencil, so there SHOULD be a way to do that in excel too,
dont you think so too?

Bommel

P.S.: i am sorry if i have posted any of my messages more than once. if i
have done so it was because of an errormessage when writing it first time
making me believe there was an error and thus my message was not posted