User defined function

V

veerababudodda

New Member
Joined
Jan 16, 2013
Messages
17
Hi all,

i have following user defined function which works well when entered. But, if i open any other workbook and work on it. the results of my udf are changing to #VALUE. please help me how can i fix it.

Function Anchor(cellValue As Variant) As Double

Application.Volatile

Dim result As Double

Dim cell As Range

Dim ws As Worksheet

Set cell = Application.Caller

Set ws = Sheet20



Dim F As Double, N As Double, K As Double, O As Double, P As Double, S As Double, T As Double, U As Double, Q As Double, R As Double

'Bolt Dia

F = cell.Offset(0, -14).Value

'H

K = cell.Offset(0, -11).Value

'LB

N = cell.Offset(0, -8).Value

'DIA

O = cell.Offset(0, -7).Value

't

P = cell.Offset(0, -6).Value

'a OR A

Q = cell.Offset(0, -5).Value

't

R = cell.Offset(0, -4).Value

'F

S = cell.Offset(0, -3).Value

'G

T = cell.Offset(0, -2).Value

'G2

U = cell.Offset(0, -1).Value





Select Case cellValue

Case 1

result = Formula1(F, N, K)

Case "1S"

result = Formula2(F, N, K)

Case 2

result = Formula3(F, N)

Case "2S"

result = Formula4(F, N)

Case 3

result = Formula5(F, N, O, P)

Case "3S"

result = Formula6(F, N, O, P)

Case 4

result = Formula7(F, N, O, P, T, S)

Case 7

result = Formula8(F, N, Q, R, T, S)

Case 5

result = Formula9(F, N, O, P, T, S, U)

Case 6

result = Formula10(F, N, Q, R, S, T)

Case Else

result = 0 ' or any other default value you want to assign

End Select



Anchor = result

End Function
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
I have no problem with your function if I open another book.

What I observe is that you have the following variables defined as Double, so if any of the cells that you use in those variables has text, then the function will return the #VALUE error.

VBA Code: 
  Dim F As Double, N As Double, K As Double, O As Double, P As Double
  Dim S As Double, T As Double, U As Double, Q As Double, R As Double
 
  F = cell.Offset(0, -14).Value 'Bolt Dia
  K = cell.Offset(0, -11).Value 'H
  N = cell.Offset(0, -8).Value  'LB
  O = cell.Offset(0, -7).Value  'DIA
  P = cell.Offset(0, -6).Value  't
  Q = cell.Offset(0, -5).Value  'a OR A
  R = cell.Offset(0, -4).Value  't
  S = cell.Offset(0, -3).Value  'F
  T = cell.Offset(0, -2).Value  'G
  U = cell.Offset(0, -1).Value  'G2


Please review the following recommendations:
Check that the cells are numbers.​
Or if any of the cells have blank spaces.​
Or if any of the variables should not be declared as Double and should be declared as String.​
It may be that the other workbook updates one of those cells with some text and that's why the function returns #VALUE.​

---------------
I arranged your code to make it easier to read:
VBA Code: 
Function Anchor(cellValue As Variant) As Double
  Application.Volatile
  Dim result As Double
  Dim cell As Range
  Dim ws As Worksheet
  Dim F As Double, N As Double, K As Double, O As Double, P As Double
  Dim S As Double, T As Double, U As Double, Q As Double, R As Double
 
  Set ws = Sheet20
  Set cell = Application.Caller
 
  F = cell.Offset(0, -14).Value 'Bolt Dia
  K = cell.Offset(0, -11).Value 'H
  N = cell.Offset(0, -8).Value  'LB
  O = cell.Offset(0, -7).Value  'DIA
  P = cell.Offset(0, -6).Value  't
  Q = cell.Offset(0, -5).Value  'a OR A
  R = cell.Offset(0, -4).Value  't
  S = cell.Offset(0, -3).Value  'F
  T = cell.Offset(0, -2).Value  'G
  U = cell.Offset(0, -1).Value  'G2
 
  Select Case cellValue
    Case 1:     result = Formula1(F, N, K)
    Case "1S":  result = Formula2(F, N, K)
    Case 2:     result = Formula3(F, N)
    Case "2S":  result = Formula4(F, N)
    Case 3:     result = Formula5(F, N, O, P)
    Case "3S":  result = Formula6(F, N, O, P)
    Case 4:     result = Formula7(F, N, O, P, T, S)
    Case 7:     result = Formula8(F, N, Q, R, T, S)
    Case 5:     result = Formula9(F, N, O, P, T, S, U)
    Case 6:     result = Formula10(F, N, Q, R, S, T)
    Case Else:  result = 0 ' or any other default value you want to assign
  End Select
  Anchor = result
End Function


---------------------​
Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.


--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​
 
Upvote 0
Hi,
I tried with your suggestions, still i am getting #value error whenever a new workbook is opened. Again i need to click on the cell where formula is there to give results.
Here is the complete code. Please do the needful.

Function anchor(cellValue As String) As Double
Application.Volatile
Dim result As Double
Dim cell As Range

Set cell = Application.Caller


Dim F As Double, N As Double, K As Double, O As Double, P As Double, S As Double, T As Double, U As Double, Q As Double, R As Double
'Bolt Dia
F = cell.Offset(0, -14).Value
'H
K = cell.Offset(0, -11).Value
'LB
N = cell.Offset(0, -8).Value
'DIA
O = cell.Offset(0, -7).Value
't
P = cell.Offset(0, -6).Value
'a OR A
Q = cell.Offset(0, -5).Value
't
R = cell.Offset(0, -4).Value
'F
S = cell.Offset(0, -3).Value
'G
T = cell.Offset(0, -2).Value
'G2
U = cell.Offset(0, -1).Value


Select Case cellValue
Case 1
result = Formula1(F, N, K)
Case "1S"
result = Formula2(F, N, K)
Case 2
result = Formula3(F, N)
Case "2S"
result = Formula4(F, N)
Case 3
result = Formula5(F, N, O, P)
Case "3S"
result = Formula6(F, N, O, P)
Case 4
result = Formula7(F, N, O, P, T, S)
Case 7
result = Formula8(F, N, Q, R, T, S)
Case 5
result = Formula9(F, N, O, P, T, S, U)
Case 6
result = Formula10(F, N, Q, R, S, T)
Case Else
result = 0 ' or any other default value you want to assign
End Select

anchor = result
End Function

Function Formula1(F As Double, N As Double, K As Double) As Double
Formula1 = WorksheetFunction.Pi() * F ^ 2 / 4 * (N + K - 8 * F + 2 * WorksheetFunction.Pi() * F) / 1000000000 * 7850 + _
1 * Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CW2:CW15")) + _
Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CX2:CX15"))
End Function

Function Formula2(F As Double, N As Double, K As Double) As Double
Formula2 = WorksheetFunction.Pi() * F ^ 2 / 4 * (N + K - 8 * F + 2 * WorksheetFunction.Pi() * F) / 1000000000 * 7850 + _
1 * Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CW2:CW15")) + _
Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CX2:CX15"))
End Function

Function Formula3(F As Double, N As Double) As Double
Formula3 = WorksheetFunction.Pi() * F ^ 2 / 4 * N / 1000000000 * 7850 + _
3 * Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CW2:CW15")) + _
Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CX2:CX15"))
End Function

Function Formula4(F As Double, N As Double) As Double
Formula4 = WorksheetFunction.Pi() * F ^ 2 / 4 * N / 1000000000 * 7850 + _
3 * Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CW2:CW15")) + _
Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CX2:CX15"))
End Function

Function Formula5(F As Double, N As Double, O As Double, P As Double) As Double
Formula5 = WorksheetFunction.Pi() * F ^ 2 / 4 * N / 1000000000 * 7850 + _
4 * Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CW2:CW15")) + _
Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CX2:CX15")) + _
WorksheetFunction.Pi() / 4 * O ^ 2 * P / 1000000000 * 7850
End Function



Function Formula6(F As Double, N As Double, O As Double, P As Double) As Double
Formula6 = WorksheetFunction.Pi() * F ^ 2 / 4 * N / 1000000000 * 7850 + _
4 * Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CW2:CW15")) + _
Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CX2:CX15")) + _
WorksheetFunction.Pi() / 4 * O ^ 2 * P / 1000000000 * 7850
End Function

Function Formula7(F As Double, N As Double, O As Double, P As Double, T As Double, S As Double) As Double
Formula7 = WorksheetFunction.Pi() * F ^ 2 / 4 * (N) / 1000000000 * 7850 + _
3 * Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CW2:CW15")) + _
Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CX2:CX15")) + _
WorksheetFunction.Pi() / 4 * O ^ 2 * P / 1000000000 * 7850 + _
WorksheetFunction.Pi() / 4 * ((T + 2 * IIf(F <= 36, 2, 2.6)) ^ 2 - T ^ 2) * S / 1000000000 * 7850 + _
2 * (1.5 * F) * (2 * F) * 6 / 1000000000 * 7850

End Function

Function Formula8(F As Double, N As Double, Q As Double, R As Double, T As Double, S As Double) As Double
Formula8 = WorksheetFunction.Pi() * F ^ 2 / 4 * (N - R) / 1000000000 * 7850 + _
2 * Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CW2:CW15")) + _
Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CX2:CX15")) + _
Q ^ 2 * R / 1000000000 * 7850 + _
WorksheetFunction.Pi() / 4 * ((T + 2 * IIf(F <= 36, 2, 2.6)) ^ 2 - T ^ 2) * S / 1000000000 * 7850 + _
2 * 0.7 * Q * (Q - 20 - F) * 0.5 * R / 1000000000 * 7850 + _
WorksheetFunction.Pi() * ((T + 2 * IIf(F <= 36, 2, 2.6)) + 20) ^ 2 / 4 * 6 / 1000000000 * 7850
End Function
Function Formula9(F As Double, N As Double, O As Double, P As Double, T As Double, S As Double, U As Double) As Double
Formula9 = WorksheetFunction.Pi() * F ^ 2 / 4 * (N + N - S - P + 2 * F) / 1000000000 * 7850 + _
4 * Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CW2:CW15")) + _
Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CX2:CX15")) + _
WorksheetFunction.Pi() / 4 * O ^ 2 * P / 1000000000 * 7850 + _
WorksheetFunction.Pi() / 4 * ((T + 2 * IIf(F <= 36, 2, 2.6)) ^ 2 - T ^ 2) * S / 1000000000 * 7850 + _
WorksheetFunction.Pi() / 4 * ((U + 2 * IIf(F <= 36, 2, 2.6)) ^ 2 - U ^ 2) * (N - S - P + 2 * F - 6) / 1000000000 * 7850 + _
2 * (1.5 * F) * (2 * F) * 6 / 1000000000 * 7850 + _
WorksheetFunction.Pi() * (U + F) ^ 2 / 4 * 6 / 1000000000 * 7850
End Function

Function Formula10(F As Double, N As Double, Q As Double, R As Double, S As Double, T As Double) As Double
Formula10 = 2 * WorksheetFunction.Pi() * F ^ 2 / 4 * N / 1000000000 * 7850 + _
2 * 3 * Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CW2:CW15")) + _
2 * Application.WorksheetFunction.XLookup(F, Worksheets("3.Data").Range("CY2:CY15"), Worksheets("3.Data").Range("CX2:CX15")) + _
2 * Q * (2 * Q + 150) * R / 1000000000 * 7850 + _
2 * WorksheetFunction.Pi() / 4 * ((T + 2 * IIf(F <= 36, 2, 2.6)) ^ 2 - T ^ 2) * S / 1000000000 * 7850 + _
2 * (150 + 1.5 * F) * (2 * F) * 6 / 1000000000 * 7850
End Function

Regards,
Veera
 
Upvote 0
Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.


Check Calculation Options is Automatic:
1689777921480.png


:cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Dynamic data rollup by Row - non specific criteria
Replies
8
Views
315
Andy395
A
S
  • Question Question
If statement
Replies
3
Views
92
etaf
etaf
S
  • Question Question
Office Script to Copy Rows From One Worksheet To Another.
Replies
2
Views
347
Fluff
Fluff
K
  • Question Question
conditional formatting question
Replies
2
Views
564
kerm007
K
H
  • Solved
Suggest me out of 2 formulas which is more reliable
2
Replies
12
Views
520
Alex Blakenburg
A

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top