Serving the Quantitative Finance Community

 
User avatar
amkey04
Topic Author
Posts: 0
Joined: January 5th, 2004, 9:19 am

Locate Cell Address in VBA

July 19th, 2004, 2:28 pm

hi,I am trying to copy the address of the range input into one of my function e.g when I insert a range E6:G9, I would like to capture E6 and G9 in my VBA program. Does anyone knows how to do this? Thanks a lot for any help in advance Amkey
 
User avatar
mrowell
Posts: 4
Joined: July 14th, 2002, 3:00 am

Locate Cell Address in VBA

July 19th, 2004, 3:07 pm

Amkey04If your VBA function looks like this:function myFunc(r as Range) ...end functionThen you can use r.Address to get the address of the range or use the following snippet to get the top left and bottom right cell addressesWith r topleft = .cells(1,1).address bottomRight = .cells(.rows.count, .columns.count).addressEnd WithregardsMark
 
User avatar
amkey04
Topic Author
Posts: 0
Joined: January 5th, 2004, 9:19 am

Locate Cell Address in VBA

July 19th, 2004, 3:34 pm

hei mrowell,Thanks for the help. I got it working now amkey
 
User avatar
amkey04
Topic Author
Posts: 0
Joined: January 5th, 2004, 9:19 am

Locate Cell Address in VBA

July 20th, 2004, 2:33 am

Hei mrowell,If I would like to insert a formula or text into one of my range cell now, what command should I use? I triedWith r .Formula = "TODAY()"End WithorWith r .Cells(5,4).Value = "=TODAY"End WithBoth doesn't work. Any suggestions?ThanksAmkey
 
User avatar
mrowell
Posts: 4
Joined: July 14th, 2002, 3:00 am

Locate Cell Address in VBA

July 20th, 2004, 5:12 am

You should use (I think)r.cells(1,1).fORMULA = "=TODAY()" basically exaclty same as you would enter into a cell yourself.Mark
 
User avatar
amkey04
Topic Author
Posts: 0
Joined: January 5th, 2004, 9:19 am

Locate Cell Address in VBA

July 20th, 2004, 5:53 am

Hi Mark,My spreadsheet contains a lot of functions. In order to increase computation efficiency, I wrote a Main Function to control the execution of various functions i.e. execution in order. This avoids random execution. This is why I need to paste my formula on some cell. However, function only returns a value on the active cell, I include a Sub in my function to print the value in another cell. My Main Function looks something like thisFunction Main (r as range, input as variant) Call testing (r) .....End MainSub testing (r as range) Range ("B6") = "=TODAY()"End SubHowever, I can't seem to print =TODAY() on cell B6. When I took r off, i.e.Sub testing () Range("B6") = "=TODAY()"End Suband use a button to control the Sub, I am able to get this job done. Can anyone explain why I cannot use a Sub within a Function to print "=TODAY()" on cell B6? or is just simply syntax error?Thanks for the helpAmkey
Last edited by amkey04 on July 19th, 2004, 10:00 pm, edited 1 time in total.
 
User avatar
mrowell
Posts: 4
Joined: July 14th, 2002, 3:00 am

Locate Cell Address in VBA

July 20th, 2004, 10:45 am

You cannot use a function to change a cell on a spreadsheet I am afraid - at least I have never managed to find a solution to this problem.Mark
 
User avatar
DominicConnor
Posts: 41
Joined: July 14th, 2002, 3:00 am

Locate Cell Address in VBA

July 21st, 2004, 10:24 am

amkey04: If you are working hard on the performance then I would recommend that you don't have any explicit calls to time based volatile functions. Better to use the VB function, and populate the cell as text.You cannot use a function to change a cell on a spreadsheet I am afraid - at least I have never managed to find a solution to this problem.True, you can't do it directly or synchronously, but you can arrange for it to happen later.I define a simple queue. Every second, the DeQue sub is called, and puts values where you tell them to go.You can fiddle with the value for this to suit your own puposes.There are a lot of things that can't be done during function calls, and you can easily extend this approach to call routines or system functions.Note you must start the timer outside the function.Type qItem what As Variant where As StringEnd TypeConst MaxQ = 200Dim QCount As LongDim Queue(MaxQ) As qItemSub DeQue()Dim i As Long Application.OnTime Now + TimeValue("00:00:01"), "deque"For i = 1 To QCount With Queue(i) Range(.where).Value2 = .what End WithNext iEnd SubSub EnQue(what As Variant, where As String) QCount = QCount + 1 Queue(QCount).what = what Queue(QCount).where = whereEnd SubFunction Asynch(value As Double) As DoubleAsynch = 42EnQue value, "a1"End FunctionSub StartAsynch() Application.OnTime Now + TimeValue("00:00:10"), "DeQue"End Sub
Last edited by DominicConnor on July 20th, 2004, 10:00 pm, edited 1 time in total.
 
User avatar
mrowell
Posts: 4
Joined: July 14th, 2002, 3:00 am

Locate Cell Address in VBA

July 21st, 2004, 10:35 am

DCFC -- thanks; very useful.CheersMark
 
User avatar
FastExcel
Posts: 3
Joined: December 2nd, 2003, 8:10 am

Locate Cell Address in VBA

July 23rd, 2004, 6:09 am

If you are using a lot of VBA functions called from worksheet cells then to improve performance make sure that- calculation is always called from VBA rather than automatically from Excel- the functions check for uncalculated input cells before executing (Use Isempty)