Filling a range of cells using a VBA function

[j00hn]

I am attempting to figure out how to fill a range of cells with values using a VBA function. Using the Value property does not work for any reason. The code below depicts my current idea:

Public Function funcA(CellRange as Range)
CellRange.Value = "MY_VALUE"
funcA = "OK"
End Function

where 'CellRange' is the range in the sheet, which should be filled with the result.

Any idea how to achieve this?

Many thanks for your help

 

[PHV]

A function called from a worksheet can't write to a cell.
How and where is called funcA ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[j00hn]

Thanks for the response, PHV.
Yes, I functA is called from a worksheet. The background for this, is that funcA is actually a wrapper around the rtd function. Eachtime when the rtd gets called, i want the result to be chunked/parsed in pieces and than to be written into multiple cells.
If this can not be achieved using VBA, are there any other suitable interfaces?

Many thanks

 

[PHV]

If this can not be achieved using VBA
This can be achieved using VBA but not from an UDF.
You may consider some worksheet's event.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

I put together some really hacked together stuff, but it may do what you want j00hn

Public Function funcA(CellRange As Range)
funcA = "x"
End Function

[green]'And in the Worksheet Code Module[/green]
Private Sub Worksheet_Change(ByVal target As Range)

Dim mysheet As String
Dim mycell As String
Dim myformula As String
[green]'Debug.Assert 0[/green]
myformula = target.Formula

If InStr(target.Formula, "funca") Then
    mysheet = Mid(myformula, InStr(myformula, "(") + 1, InStr(myformula, "!") - InStr(myformula, "(") - 1)
    mycell = Mid(myformula, InStr(myformula, "!") + 1, InStr(myformula, ")") - InStr(myformula, "!") - 1)
    Sheets(mysheet).Range(mycell).Value = 1
End If

End Sub

I should note a few things: This will only work on a sheet you put the code into it's module

and

this is really hacked together, it isn't elegant or neat or clean. Currently it requires that you specify the sheet of the cell you want to change, even if it's the same sheet. You can change this yourself by playing with it.

Anyway, if you find it useful, enjoy!

 

[j00hn]

Many thanks for the suggestion and the example code.
The SheetCalculate event should do the job in my case,
because it is fired whenever the sheet is recalculated.

 

[Gruuuu]

That would certainly be reasonable if the address in funcA changes dynamically, like if you're using an INDIRECT or OFFSET function as well. Otherwise the Worksheet_Change event will be more efficient.