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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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

G
  • Question Question
Why does #VALUE error in VBA function occur when creating a ReDim Matrix of Stock prices from a Named Range?
Replies
2
Views
103
Peter_SSs
Peter_SSs
U
  • Solved
Rewrite Code as a Sub to work on Active Sheet
Replies
2
Views
53
Skyybot
Skyybot
G
  • Question Question
Extract Left Of A Upper Case Letter
Replies
1
Views
82
hagia_sofia
hagia_sofia
montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
137
montecarlo2012
montecarlo2012
M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
122
Mazurofgod
M

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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