UDF as per caller worksheet

[rajkle]

Hi,
I wanted to explore excel feature. Recently I have started writing UDF
(User Defined Functions) in excel which will help in my day to day life. I stuck in some problem Can you please help me on below things
1 . In my workbook I have many sheets. In each sheet I have used same UDF
functions which will parse some range of cells and extract relevant data. Issue is
UDF function takes active sheet data (if I open workbook or press
Clt+Alt+Shift+f9). Logically it is true but I want UDF function operates on
caller worksheet data (i.e If it is called in sheet1, it should take all data from Sheet1, but currently if I press Clt+Alt+Shift+f9 in sheet1, Sheet1, Sheet2 all will have as per sheet1 data). Please let me know if you have any suggestion/solution.
2. I have three column A,B,C and in next sheet I will use C' column as A' +
B' = C', where A' = A+B) and so on. Since sheet names will have fixed pattern
I wrote a UDF which will fetch this but it shows Circular reference error. Is
any workaround is there

 

[vletm]

rajkle
As You wrote that You've done own UDF ...
Now, You would like it work other ways ... even without error.
It would be a challenge without a Sample file,
which have You UDF; including sample of needed result.

 

[rajkle]

Function myData(Key As String, col As String, low As String, high As String) As String
Dim Final As String
Dim MyRange As Range

col = Left(col, Len(col) - 1)
low = CInt(Left(low, Len(low) - 1))
high = CInt(Left(high, Len(high) - 1))
Set MyRange = Range(col & low & ":" & col & high)

If (Len(Key)) <> 0 Then
    For j = 1 To MyRange.Count
        CellRef1 = MyRange(j)
        If InStr(CellRef1, Key) Then Final = Final & Cells(MyRange(j).row, MyRange(j).Column + 1) & " + "
    Next j
End If

myData = Final
Range(Application.Caller.Address).WrapText = True
Range(Application.Caller.Address).EntireRow.AutoFit
End Function

=================================================
I have mentioned my vba code and attach test.xls file. If you add this module (Alt+F11, insert module). Assume you are in sheet1 and Press Clt+Alt+Shift+F9, you will see sheet2 result will change as per sheet1. I want If I Press Clt+Alt+Shift+F9, VBA function find the caller sheet and fetch & parse data as per caller sheet not as per activate sheet.

 

[vletm]

rajkle
Not correct file!
and
both sheets G5-cell has 'a link' or something?
What would be needed value for it?

 

[rajkle]

rajkle
Not correct file!
and
both sheets G5-cell has 'a link' or something?
What would be needed value for it?

I am able to open test.xlsx. What error your are getting with file?
G4 consist UDF function. To work properly Do following thing
1. Open test.xlsx file and Press Alt+F11
2. After pressing Alt+f11 you will see VBA editor. Using option insert you need to add module. In module1 paste mentioned code (in last mail)
3. Now if your refresh G4 cell in both sheet you will see proper value as per UDF

Only thing is , it is taking active sheet data

 

[vletm]

1) File is correct that way. It can open.
But I don't have 'rajAddIn.xlam'-file, where would be Your UDF.
2) So far ... xlsx-files do cannot have any code!
3) What would be proper, needed values?

 

[rajkle]

RajAddin is not required. basically i have used this to make sure UDF should available for all workbook.

Function myData(Key AsString, col AsString, low AsString, high AsString) AsString
Dim Final AsString
Dim MyRange As Range

col = Left(col, Len(col) - 1)
low = CInt(Left(low, Len(low) - 1))
high = CInt(Left(high, Len(high) - 1))
Set MyRange = Range(col & low & ":" & col & high)

If (Len(Key)) <> 0 Then
   For j = 1 To MyRange.Count
        CellRef1 = MyRange(j)
       If InStr(CellRef1, Key) Then Final = Final & Cells(MyRange(j).row, MyRange(j).Column + 1) & " + "
   Next j
EndIf

myData = Final
Range(Application.Caller.Address).WrapText = True
Range(Application.Caller.Address).EntireRow.AutoFit
EndFunction

add above code in VBA editor

 

[vletm]

rajkle


Of course that other file no need!
But why You didn't send correct file which could use?
... and ...
that UDF won't work, if I would press Clt+Alt+Shift+F9!
... that won't be a challenge!

Okay, if Sheet2 needs student+rich+
You've tried to send a code, which won't work as You need.
Could You explain
... write 'what is missing?'
= what should be the result?

 

[rajkle]

rajkle
View attachment 57310

Of course that other file no need!
But why You didn't send correct file which could use?
... and ...
that UDF won't work, if I would press Clt+Alt+Shift+F9!
... that won't be a challenge!

Okay, if Sheet2 needs student+rich+
You've tried to send a code, which won't work as You need.
Could You explain
... write 'what is missing?'
= what should be the result?

That was huge so didn't send.I want to use Clt+Alt+Shift+F9 to make sure all formula is calculated.

Expected result in Sheet1 : raj +
Expected result in Sheet2 : student + rich +

But after pressing Clt+Alt+Shift+F9! (if sheet2 is activated)
Expected result in Sheet1 :
Expected result in Sheet2 : student + rich +

But after pressing Clt+Alt+Shift+F9! (if sheet1 is activated)
Expected result in Sheet1 : rich +
Expected result in Sheet2 :

 

[vletm]

rajkle
Huge
... sometimes size no matter
... You could do a sample file which works.

Your 1st expected result...
... How it would be in Sheet1 as raj + ?
... ... later rich +

Can You see more than one sheet in time?
... if 'yes' then maybe something else than UDF.
If one
... then UDF would work .. or something without those four keys.
I would do it something like this,
... without UDF
... of course, there are rules for many 'Your parameters' and so!

 

[rajkle]

Okay I will explain briefly .
1. Why I want to use Clt+Alt+Shift+F9
- Since file is huge and lot of place UDF is there so manually refresh is painful so with this Clt+Alt+Shift+F9 I want every time when changes happen all cells update properly (with volatile I can do but it will be time consuming)
2. Come to Test.xlsx
Objective : whatever key word will be there in A2 (i.e raj), Code will search in mentioned column in cell B2 (i.e C). Code will search keyword in column C, row range is mentioned in cell C2 and D2 (i.e 5 and 25).

Now in Sheet2, Code will find raj keyword in range C5:C25 and prepare string from next column.
Now in Sheet1, Code will find raj keyword in range B8:B25 and prepare string from next column.

since only one place UDF is used in each sheet I call manually refresh (select specific cell and press enter key). It will work as per my expectation. When UDF is called at multiple places manual refresh is painful. We need something which can perform this refresh in one click. "Calculate sheet" option is one thing which I can use but I saw sometimes it is not working. But Clt+Alt+Shift+F9 is force refresh which works perfectly fine. But issue with this approach is activate sheet. In Excel user can see only one sheet at a time and user wants to refresh all UDF function present in all sheets so user will to press Clt+Alt+Shift+F9 key to refresh all sheet. If user does this he will see all sheets data is updates but as per current sheet(where user pressed Clt+Alt+Shift+F9) but intention was UDF should work based on sheet from where UDF is called, like in sheet1 I am asking search keyword in B column and sheet2 I am asking search keyword in column C. So my question is there any way to get rid of this scenario.

 

[vletm]

no