Page 1 of 1

vb question

Posted: January 21st, 2012, 7:55 am
by player
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

vb question

Posted: January 21st, 2012, 8:08 am
by vrat
I found return value missing

vb question

Posted: January 21st, 2012, 8:31 am
by player
indeed, but how do i resolve this?

vb question

Posted: January 21st, 2012, 9:26 am
by Govert
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

vb question

Posted: January 21st, 2012, 4:34 pm
by vrat
Use UDF(User defined Function)

vb question

Posted: January 21st, 2012, 5:45 pm
by bearish
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

vb question

Posted: January 21st, 2012, 6:20 pm
by Cuchulainn
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

vb question

Posted: January 21st, 2012, 8:44 pm
by player
actually the question was clear and the answer given by Govert was very clear.Thank for the help Govert.

vb question

Posted: January 22nd, 2012, 9:10 am
by Cuchulainn
Fair enough. The answer by @bearish was the most accurate one, though. Your function had a fundamental bug.

vb question

Posted: January 22nd, 2012, 4:19 pm
by Cuchulainn
here's C code using VB Style; OK? It's a novel way to do it and should no give any problems?

vb question

Posted: February 12th, 2012, 3:27 pm
by MikeJuniperhill
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 ..