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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

K
  • Question
conditional formatting question
Replies
2
Views
163
kerm007
K
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
171
Tetra201
Tetra201
gtd526
  • Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
140
gtd526
gtd526
E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
141
Peter_SSs
Peter_SSs
E
  • Solved
Summing Values
Replies
3
Views
218
LazyBug
LazyBug

Forum statistics

Threads
1,215,093
Messages
6,123,069
Members
449,090
Latest member
fragment

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