Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110454

Re: Enter value in compute cell location?

Show all headers | View raw

On 1/16/2018 7:06 PM, Jianguo Li wrote:
> Worksheet function can't change the other cells' value. It's the Excel's
> rule.

Explicitly, yes; but "rules are made to be broken!"  :)

> And I don't understand what you say.

OK, here's the working implementation --

Function SetCell(RefCell As Range, _
          ColumnOffset As Long, _
          SourceCell As Range, _
          TargetCell As Range)
' Writes value from SourceCell at TargetCell plus above column Offset
' relative to RefCell, the location containing the function expression

   offSrce=SourceCell.Column-RefCell.Column ' offset to source data
   offTarg=TargetCell.Column-RefCell.Column+ColumnOffset ' offset target

   ' Build call to pass to EVALUATE method as
   '    SetValue(RefCell,OffSetData,OffSetTarget)

   cmd = "SetValue(" & RefCell.Address(False, False) & "," & _
                                       offSrce & "," & offTarg & ")"
   ' Pass command string to Evaluate to execute Sub
   RefCell.Parent.Evaluate cmd

   ' Satisfy the "rule" to set value of RefCell where the function lives
   SetCell = ""    ' empty .Value doesn't destroy .Formula so
End Function


Sub SetValue(RefCell As Range, offSrce As Long, offTarg As Long)
' Set cell at OFFSET(REFCELL(0,offTARG) with value from
'              OFFSET(REFCELL(0,offSRCE)
' Uses SUB to do dirty work since UDF can't directly alter itself

     RefCell.Offset(0, offTarg).Value = RefCell.Offset(0, offSrce).Value
End Sub

And, just for funs, another function found that allows you to add 
IntelliSense help for UDFs...

Sub DescribeFunction()
    Dim FuncName As String
    Dim FuncDesc As String
    Dim Category As String
    Dim ArgDesc(1 To 3) As String

    FuncName = "SETCELL"
    FuncDesc = "Sets cell at offset to target origin to source value"
    Category = 14 ' user defined
    ArgDesc(1) = "Range of Cell Containing Function"
    ArgDesc(2) = "Offset from Target Origin as Value"
    ArgDesc(3) = "Range (Cell) Containing Source Data"
    ArgDesc(4) = "Range (Cell) Origin for Target Data"

    Application.MacroOptions _
       Macro:=FuncName, _
       Description:=FuncDesc, _
       Category:=Category, _
       ArgumentDescriptions:=ArgDesc
End Sub

Execute the above only once after you've got the user interface for the 
function defined in its final form.

EXAMPLE USE:

Entering

   =SetCell($U9,MATCH(H9,LookupValues,0)-1,D9,V9)

in U9 writes the value in D9 at V9 plus the offset determined by the 
MATCH index found for the value H9 found in the table LookupValues.  The 
values in LookupValues table must be in the order of the desired column 
in the target, of course. (The "minus one" just converts to 0-based 
indexing from the one-based result returned by MATCH.)

The "trick" is using the SUB to do the dirty work and .Evaluate method 
to do so.  I still haven't determined why one can't CALL the SUB; see 
the earlier followup.

Took some tinkering and isn't _quite_ as clean as would like but does 
save the manual copying of a large target area in lieu of simply the one 
column.  As conventional programming language user almost exclusively, 
the overhead in the need to copy everything over and over and over again 
in Excel is just horribly frustrating; this is one small step away from 
that.

Wouldn't recommend for random use; as noted this was, in part, a quest 
to break up the otherwise monotonous...

--

--

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Enter value in compute cell location? dpb <none@none.net> - 2018-01-16 16:26 -0600
  Re: Enter value in compute cell location? dpb <none@none.net> - 2018-01-18 13:56 -0600
  Re: Enter value in compute cell location? "Jianguo Li" <2@1.com> - 2018-01-17 09:06 +0800
    Re: Enter value in compute cell location? dpb <none@none.net> - 2018-01-22 08:39 -0600
      Re: Enter value in compute cell location? dpb <none@none.net> - 2018-01-22 08:51 -0600
      Re: Enter value in compute cell location? killme2008 <killme2008.14adfdf8@excelbanter.com> - 2018-01-24 08:11 +0000
  Re: Enter value in compute cell location? Gerry Timber <swa2194@gmail.com> - 2018-01-24 03:34 -0800
    Re: Enter value in compute cell location? dpb <none@none.net> - 2018-01-24 09:26 -0600
    Re: Enter value in compute cell location? GS <gs@v.invalid> - 2018-01-24 10:37 -0500
    Re: Enter value in compute cell location? GS <gs@v.invalid> - 2018-01-24 19:09 -0500
      Re: Enter value in compute cell location? GS <gs@v.invalid> - 2018-01-24 19:12 -0500
  Re: Enter value in compute cell location? Gerry Timber <swa2194@gmail.com> - 2018-01-24 12:07 -0800
  Re: Enter value in compute cell location? GS <gs@v.invalid> - 2018-01-24 19:29 -0500

csiph-web