PDA

View Full Version : Troubleshooting Function



CatDaddy
08-15-2012, 01:02 PM
I am working on a pretty complicated function right now and I have come up against a bit of a road block. I suppose I have a few quick questions.

I'm making this function for direct use from the worksheet and I am trying to affect other cells besides the one the formula is typed in.

1) Is this possible? If so how do I go about referencing the cell in which the formula is entered (my guess was ActiveCell)?

2) Is there a way to "step into" a function the same way you can debug a subroutine, but this time with input straight from the formula box?

3) If I wanted one of the parameters of the function to be entered as a series of columns (either "A:B,D:E,G:F" or "1:2,4:5,7:8") which I parse in the body of the function as a string, will it be a problem that the comma is already the delimeter between arguments in the formula box? If so could the range of columns be entered inside of quotations (or would that read into the string literal)?

Thanks a million

Bob Phillips
08-15-2012, 01:08 PM
1) There are some convoluted workarounds that work in some situations, but realistically, no.

2) You can step into a function just as a sub, but not sure what you mean by input straight from the formula box.

3) Better to have one of the arguments, the last one, a ParamArray and process each element within a loop.

CatDaddy
08-15-2012, 01:22 PM
1) how convoluted? how about like a sub call in the function?
2) I tried to just step into it but it needs parameters, how do i feed them in?
3) OK

Bob Phillips
08-15-2012, 01:48 PM
1) No, much more work than that. I would have to lookup how it is done, it isn't something I keep in the forefront, or ever use - there are simpler alternatives.

2) Just put a break on the Function signature and then F2 the worksheet call.

CatDaddy
08-15-2012, 02:06 PM
Application.Caller.Address?

Paul_Hossler
08-15-2012, 05:14 PM
I didn't grasp your other questions. but Application.Caller will contain the cell where the formula is.

I don't think that a WS function can directly change other cells


Option Explicit
Function CatDaddy_101() As Variant
CatDaddy_101 = Application.Caller.Column
End Function



Paul

CatDaddy
08-16-2012, 11:38 AM
ok so Application.ThisCell has given me the right cell reference but when i debug the code just breaks at this line (no error, it just stops)

For j = (s + 1) To e
off2 = j - off2
cell.Offset(0, off).Value = Application.WorksheetFunction.VLookup(src, Worksheets(sht).Range(rng), off2, ex)
off = off + 1
Next j

Previously I used:
Set cell = Application.ThisCell
And I checked during the debug and it is assigning the correct range to cell

CatDaddy
08-16-2012, 12:46 PM
I tried putting in Application.Calculate after the place where i set the value of the function to force calculations (someone mentioned to me that the reason you typically can't alter other cells with a function is that excel is making an effort to keep the relationships between cells consistant during calculation)

My code is still breaking on the line where i try to set the value of another cell but it is pulling in all of the information correctly.

CatDaddy
08-16-2012, 01:13 PM
So far the only passable solution I have come accross on the web is to have a Worksheet_Calculate event associated with the function and some global variables, but i would really like to do this another way.

snb
08-16-2012, 02:15 PM
You could simulate a function using the calculate event:

Private Sub Worksheet_Calculate()
If ActiveCell.Address = "$B$6" Then ActiveCell.Offset(, 5) = 9
End Sub

IN cell B6 the formula

=If(Today(),"")


or if you want it to apply to several cells:

Private Sub Worksheet_Calculate()
If ActiveCell.Formula = "=IF(TODAY(),"""")" Then ActiveCell.Offset(, 5) = 9
End Sub

CatDaddy
08-17-2012, 10:01 AM
Ok so I understand there are ways to manipulate this situation to give an illusion of what I am trying to accomplish but I would really like to make this a standalone worksheet function (purely as a matter of scientific interest)

CatDaddy
08-17-2012, 12:16 PM
I came accross some information that led me to believe i might be able to accomplish what im looking for if my return of the function is an array (variant), do y'all know anything about that?

Bob Phillips
08-17-2012, 12:30 PM
That might work, but how about making it a UDT?

CatDaddy
08-17-2012, 02:46 PM
would that somehow make the result of the formula write to multiple cells?

I dont understand how I would apply that here...right now i was trying something along these lines:
Public Function MEGAV(ByVal src As Variant, ByVal shtRng As String, ByVal ex As Boolean, ParamArray cols() As Variant) As String()
Dim choice As Boolean
Dim spl, spl2 As Variant
Dim results() As Variant
Dim R_FINAL() As String
Dim sht, rng, col As String
Dim i, s, e, l, r, off2, off, colI As Integer
Dim test As String
choice = IsNumeric(src)
r = 1

'........................

ReDim R_FINAL(0, (UBound(results) - 1))
For i = LBound(results) To UBound(results)
R_FINAL(0, (i - 1)) = CStr(results(i))
Next i
MEGAV = R_FINAL
End Function

Bob Phillips
08-18-2012, 03:46 AM
No, as I said it is not really doable.

I thought that as you mentioned returning an array you wanted multiple values returned by the function (within VBA). A UDT would just bee a more elegant, type version of an array.

You could write a UDF that wrote to an array block. As an example, select say A1:A10 and array-enter this formula

==MyFunction(TODAY(),"week")

with this UDF

Function myFunction(inDate As Date, inType As String) As Variant
Dim nextDate As Date
Dim i As Long
Dim cCells As Long
Dim tmpArray() As Date

nextDate = inDate
cCells = Application.Caller.Cells.Count
ReDim Preserve tmpArray(0 To cCells)
For i = 1 To cCells
tmpArray(i - 1) = nextDate
Select Case LCase(inType)
Case "day": nextDate = nextDate + 1
Case "week": nextDate = nextDate + 7
Case "month": nextDate = nextDate + 30
Case "year": nextDate = nextDate + 365
End Select
Next i

If Application.Caller.Rows.Count = 1 Then
myFunction = tmpArray
Else
myFunction = Application.Transpose(tmpArray)
End If
End Function