function with more functionality then typical UDFs?

[xtriant]

Hello,

I would like to create a function, such that if I type

"=testfunction(noRows, noCols)"

into a cell in Excel, say A1, then the range beginning from A1 until
cell(noRows, noCols) is filled with "1". Of course, this isn't
possible with UDFs in VBA, but I have seen it before with some special
programs which can be added/installed into Excel and provide such a
functionality. I was thinking about implementing a C#/.NET method that
way, save it in some file, and everyone who wants to use this function
would then just need to install/reference(?) this file that I would
provide him.

Any ideas/suggestions/links/keywoards which I can look up?

Thanks a lot in advance!

 

[Bob Phillips]

Why not just select an area noRows x noCols, enter a 1 in the formula bar,
and then hit Ctrl-Enter?


HTH

Bob

"xtriant" wrote in message

Hello,

I would like to create a function, such that if I type

"=testfunction(noRows, noCols)"

into a cell in Excel, say A1, then the range beginning from A1 until
cell(noRows, noCols) is filled with "1". Of course, this isn't
possible with UDFs in VBA, but I have seen it before with some special
programs which can be added/installed into Excel and provide such a
functionality. I was thinking about implementing a C#/.NET method that
way, save it in some file, and everyone who wants to use this function
would then just need to install/reference(?) this file that I would
provide him.

Any ideas/suggestions/links/keywoards which I can look up?

Thanks a lot in advance!

 

[xtriant]

Hi Bob, thanks for the quick answer - but to fill the range with 1s
was just a simple example.

Later I will need to fill the range with different input which is
going to be specified in the testfunction and which in fact will take
more parameters than just noRows and noCols.

 

[AB]

I'd go with and .xla that your users can reference, the .xla would
have a userform that could be called with a keyboard shortcut and the
userform would capture the parameters used for populating the range.
Then on the form there would be click button that would call a code
to populate the range.

 

[xtriant]

Thanks for the suggestion; however, I will really need the
"testfunction" to be later a complicated C#-method. Also for
convenience, it should be built that way that one only needs to type
in "=testfunction(...)" into a cell having the applications later in
mind.

 

[AB]

I could be mixing apples & oranges here but I thought that MS
explicitly removed the functionality where a function could actually
alter the data in other cells - because of security issues.
Perhaps here shows also my lack of knowledge of c# - I'm very much
just a vba person.
In theory what you need can still be achieved with .xla and vba - you
can create a class withevents ws and then have ws_change event check
what exactly changed and if a user entered formula
"=testfunction(...)" then do such & such.
But perhaps there are way much more elegant solutions to accomplish
the same thing.

 

[Master Blaster]

Perhaps this helps you further:

in a module use this:
----------------------------------------------------------------------
Function testfunction(a, b As Integer)
stringtp = a & "," & b
testfunction = stringtp
End Function
----------------------------------------------------------------------
on the worksheet use this:

----------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("a1"), Target) Is Nothing Then

ar = ActiveCell.Row
ac = ActiveCell.Column

st1 = Cells(1, 1)
dd = Left(st1, 1)
ee = Right(st1, 1)

qq = InputBox("fill-in")

For r = ar To ar + dd
For c = ac To ac + ee
Cells(r, c) = qq
Next c
Next r

End If
End Sub

 

[xtriant]

Perhaps this helps you further:

in a module use this:
----------------------------------------------------------------------
Function testfunction(a, b As Integer)
stringtp = a & "," & b
testfunction = stringtp
End Function
----------------------------------------------------------------------
 on the worksheet use this:

----------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("a1"), Target) Is Nothing Then

ar = ActiveCell.Row
ac = ActiveCell.Column

st1 = Cells(1, 1)
dd = Left(st1, 1)
ee = Right(st1, 1)

qq = InputBox("fill-in")

For r = ar To ar + dd
  For c = ac To ac + ee
     Cells(r, c) = qq
  Next c
Next r

End If
End Sub

Thanks very much for the answers to far but I am afraid, my problem
remains. In fact, the best way for me would be to not touch VBA at
all. I really need to work with a C# method later which would
determine the input of the different cells in the range.

I am thinking of somehow doing it using a COM-Add-In, created with
Visual Studio C#/.NET - which can then be installed/referenced into
Excel. But all examples/code in this direction which I have found were
not helpful.

Any ideas in this direction? Or in another direction which does not
involve touching VBA?

 

[xtriant]

What AB said; a cell function can not write to remote cells.

So you are confusing methods and languages. Whether this is VBA or VB  or
VB.NET or C# makes no difference, you simply can not do this with a cell
function. But you can do it with what VB/VBA knows as a Sub, in a xla or
xlam addin, or a COM addin, no matter which language it's written in.

HTH. Best wishes Harald

Unfortunately, I still don't know how to proceed. I guess, I need to
learn some basics on xll-Addins in order to see how I can build my
solution which the user just needs to add and then access the
"testfunction" as described above. If anyone has a link/suggestion how
I can learn more in this area, I'd be very grateful.

 

[xtriant]

Unfortunately, I still don't know how to proceed. I guess, I need to
learn some basics on xll-Addins in order to see how I can build my
solution which the user just needs to add and then access the
"testfunction" as described above. If anyone has a link/suggestion how
I can learn more in this area, I'd be very grateful.

Thanks to all for the answers,
I consider the thread closed.