Serving the Quantitative Finance Community

 
User avatar
player
Topic Author
Posts: 0
Joined: August 5th, 2002, 10:00 am

vb question

January 21st, 2012, 7:55 am

A ridiculously simple question but since I havent used vb for a long time Im gonig mad at this.I have written this function Function output(i As Double, j As Double)If i > j ThenWith Range("A1").Offset(1, 1) = 3End WithEnd IfHowever the program crashes at the line .Offset(1,1)=3When I write it as a subrountine it works but as a function it crashes. What am I doing wrong? In particular how can I output a value in excel from the vb editor in a function? eg even this does not work Function output(i As Double, j As Double)If i > j ThenRange("A1") = 5End IfEnd Function
Last edited by player on January 20th, 2012, 11:00 pm, edited 1 time in total.
 
User avatar
vrat
Posts: 0
Joined: December 6th, 2011, 6:58 am

vb question

January 21st, 2012, 8:08 am

I found return value missing
 
User avatar
player
Topic Author
Posts: 0
Joined: August 5th, 2002, 10:00 am

vb question

January 21st, 2012, 8:31 am

indeed, but how do i resolve this?
 
User avatar
Govert
Posts: 0
Joined: January 28th, 2006, 10:02 am

vb question

January 21st, 2012, 9:26 am

In Excel, you can't set values in other cells from a worksheet calculation context.So this function fails when called from a worksheet cell A1, but works fine when called from the Immediate Windows or another Sub()Function Test() As String Range("B1").Value = "XXX" Test = "Hello"End Function-GovertExcel-DNA - Free and easy .NET for Excel
 
User avatar
vrat
Posts: 0
Joined: December 6th, 2011, 6:58 am

vb question

January 21st, 2012, 4:34 pm

Use UDF(User defined Function)
 
User avatar
bearish
Posts: 5186
Joined: February 3rd, 2011, 2:19 pm

vb question

January 21st, 2012, 5:45 pm

A confused question followed by confusing answers...If you actually want a function to return 3 if i>j and a blank cell otherwise, then this will do it:Function output(i As Double, j As Double) If i > j Then output=3 Else output=""End Function
 
User avatar
Cuchulainn
Posts: 20252
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

vb question

January 21st, 2012, 6:20 pm

Indeed.I am wondering what happens in OP code if i <= j? Looks like a error (disclaimer: I have not done VB for a few years).QuoteWhen I write it as a subroutine it works but as a function it crashes. What am I doing wrong?Function body has bug. Must always return a value (all paths in function graph must be reachable, in your case they are not). A subroutine does sometime internally. It works in this case for the wrong reasons, unfortunately. QuoteIn VB, you can pass an argument to a procedure (subroutine or function) by value or by reference by specifying the "ByVal" or "ByRef" keywords. By default a subroutine and a function treat their arguments as by value, which means that the procedure cannot modify the contents of the variable element in the calling code. A function will return a value by assigning the value to the function name, or include it in the "Return" statement. A subroutine normally doesn't return a value, but you can define an argument as "ByRef" to return a value as a parameter. However the program crashes at the line .Offset(1,1)=3In fact, it crashes at (i,j) for all i <= j
Last edited by Cuchulainn on January 20th, 2012, 11:00 pm, edited 1 time in total.
 
User avatar
player
Topic Author
Posts: 0
Joined: August 5th, 2002, 10:00 am

vb question

January 21st, 2012, 8:44 pm

actually the question was clear and the answer given by Govert was very clear.Thank for the help Govert.
 
User avatar
Cuchulainn
Posts: 20252
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

vb question

January 22nd, 2012, 9:10 am

Fair enough. The answer by @bearish was the most accurate one, though. Your function had a fundamental bug.
Last edited by Cuchulainn on January 21st, 2012, 11:00 pm, edited 1 time in total.
 
User avatar
Cuchulainn
Posts: 20252
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

vb question

January 22nd, 2012, 4:19 pm

here's C code using VB Style; OK? It's a novel way to do it and should no give any problems?
Last edited by Cuchulainn on January 21st, 2012, 11:00 pm, edited 1 time in total.
 
User avatar
MikeJuniperhill
Posts: 4
Joined: February 7th, 2004, 11:57 am

vb question

February 12th, 2012, 3:27 pm

QuoteOriginally posted by: playerA ridiculously simple question but since I havent used vb for a long time Im gonig mad at this.I have written this function Function output(i As Double, j As Double)If i > j ThenWith Range("A1").Offset(1, 1) = 3End WithEnd IfHowever the program crashes at the line .Offset(1,1)=3When I write it as a subrountine it works but as a function it crashes. What am I doing wrong? In particular how can I output a value in excel from the vb editor in a function? eg even this does not work Function output(i As Double, j As Double)If i > j ThenRange("A1") = 5End IfEnd FunctionI am late as usual, but still wanted to comment this. VBA functions called from worksheet have some limitations. For example, they can not change any cells, apply formatting, open workbooks, etc. If the function attemps to do any of these things, the formula return an error. This is the reason, why your function crashes at that line. So, VBA functions called from worksheet can only perform calculations and return value(s). One exception to this rule is VBA MsgBox ..
Last edited by MikeJuniperhill on February 11th, 2012, 11:00 pm, edited 1 time in total.