(Solved) – Writing to a worksheet from a VBA function

(solved)-–-writing-to-a-worksheet-from-a-vba-function

I am trying to write some intermediate results in a user defined VBA function to a worksheet. I have tested the function, and it works correctly. I am aware that I cannot modify / write to cells from a UDF, so i tried passing the relevant results to a subroutine which I hoped would then be able to write to the spreadsheet. Unfortunately my scheme doesn’t work, and I’d be very appreciative if someone would help me think through this problem.

Public Function f(param1, param2)
    result = param1 * param2
    call writeToSheet(result)
    f = param1   param2
end

public sub writeToSheet(x)
    dim c as range
    c = range("A1")
    c.value = x
end 

I would like to see the product of param1 and param2 in cell A1. Unfortunately, it does not happen – the subroutine just ends abruptly as soon as it attempts to execute the first statement (c = range(“A1”) ). What am i doing wrong, and how can i fix it?

If it is simply impossible to write to a spreadsheet in this way, is there some other way in which to store intermediate results for later review? My real life problem is a little more complicated that my stylized version above, as i generate a new set of intermediate results each time i go through a loop, and want to store them all for review.

Sincerely and with many thanks in advance

Thomas Philips

Leave a Reply

Your email address will not be published. Required fields are marked *