# how to run a function from sub

• ### Question

• Hi,

Is there any simple way to run a function from a sub ? If function name is evaldata ?

The function works like this in a cell ,  say in B1 =evaldata(A1)

With this formula it works fine. Better if it can be applied to whole workbook without formula .

I want it to run in

Private Sub Worksheet_Calculate()

evaldata

End Sub

Can I get it done ?

regards

Friday, November 6, 2015 12:41 PM

### All replies

• E.g.

```Public Sub MySub()

Dim Result As Variant ' Use the return type of your function

Result = evaldata(yourParameters)

Debug.Print  Result

End Sub
```

Friday, November 6, 2015 12:45 PM
• Thank you ,

I am sorry but I could not make it work.

Do I have to put whole code at "your parameter" ?

regards

Friday, November 6, 2015 1:11 PM
• Can you please tell me , What are parameters here , how it is entered there .

```  Dim i As Long, j As Long
Dim Digit As String
If IsObject(What) Then What = What(1, 1).Value
What = Replace\$(What, " ", "")  What = Replace\$(What, "'''", "''")
What = Replace\$(What, "''", """")
j = 1
For i = 1 To Len(What)
Digit = Mid\$(What, i, 1)
Select Case Digit
Case "'"
'Feet
EvaluateFeetAndInch = EvaluateFeetAndInch + CDbl(Mid\$(What, j, i - j))
j = i + 1
Case """"
'Inch
EvaluateFeetAndInch = EvaluateFeetAndInch + CDbl(Mid\$(What, j, i - j)) / 12
j = i + 1
End Select
Nextregards```
Sunday, November 8, 2015 9:51 AM
• Alt-F11 to go to the Visual Basic Editor

Insert, Module

assuming you want to fill column B with calculated values based on column A, perhaps something like this:

'~~~~~~~~~~~~~~~~~~~~~

Sub RunMyFunction()
'crystal
On Error GoTo Proc_Err
Dim ws As Excel.Worksheet
Dim nRow As Long

For Each ws In ActiveWorkbook
For nRow = 2 To ws.Rows.count 'start at 2 -- assuming you have a header row
ws.Cells(nRow, 2) = evaldata(ws.Cells(nRow, 1))
Next nRow
Next ws

Proc_Exit:
On Error Resume Next
'release object variables
Set ws = Nothing
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& "   RunMyFunction"

Resume Proc_Exit
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~~~

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions or operating environment, or modify objects with code behind them, you should always compile and save before executing.

from the menu in a VBE (module) window: Debug, Compile

Fix any errors on the highlighted lines.
Add needed references and remove missing references if necessary
(Tools, References...)

keep compiling until nothing happens (this is good!) -- then Save

Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

Tuesday, November 10, 2015 3:40 AM
• Hi Thank you ,

Error 438,

Object doesn't support this property or method.

regards

Tuesday, November 10, 2015 1:28 PM
• you need to substitute YOUR function name for evaldata

I only used that because you did ... I do not know what the name of the function you want to call is.  What is it?  Perhaps you can post the code for it?

What line does the compiler stop on when you get the message?

Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

Tuesday, November 10, 2015 5:16 PM
• Hi ,

Thank you,

I did but above error shown.

You can make change for this function

`Option ExplicitFunction Feet(ByVal what As Variant) As Double  Dim i As Long, j As Long    Dim Digit As String    If IsObject(what) Then what = what(1, 1).Value    what = Replace\$(what, " ", "")    what = Replace\$(what, "'''", "''")    what = Replace\$(what, "''", """")    j = 1    For i = 1 To Len(what)    Digit = Mid\$(what, i, 1)    Select Case Digit    Case "'"                    'Feet    Feet = Feet + CDbl(Mid\$(what, j, i - j))    j = i + 1    Case """"                   'Inch    Feet = Feet + CDbl(Mid\$(what, j, i - j)) / 12    j = i + 1    End Select    Next End Functionregards`

Wednesday, November 11, 2015 1:53 AM
• >>>Error 438,

Object doesn't support this property or method.<<<

According to you description, I have made a sample and reproduce this issue, so I suggest that you could modify your code like below:

```For Each ws In ActiveWorkbook.Sheets

......

Next ws
```

Thursday, November 12, 2015 9:58 AM
• Thank you David_JunFeng ,

Now excel workbook freezes , not working.

regards

Thursday, November 12, 2015 1:04 PM
• >>>Now excel workbook freezes , now working.

I have reproduced this issue, There are 1048576 rows in Worksheet object that cause workbook freezing.
So I suggest that you could select Range then use your "Feet" function, you could refer to below:

```For Each ws In ActiveWorkbook.Sheets
Set curRange = ws.Range("A1:B30")
For nRow = 2 To curRange.Rows.Count 'start at 2 -- assuming you have a header row
curRange.Cells(nRow, 2) = Feet(curRange.Cells(nRow, 1))
Next nRow
Next ws```

Friday, November 13, 2015 8:47 AM
• Hi

now error here

`curRange.Cells(nRow, 2) = Feet(curRange.Cells(nRow, 1))regards`

Friday, November 13, 2015 11:14 AM
• >>>now error here
curRange.Cells(nRow, 2) = Feet(curRange.Cells(nRow, 1))<<<

I have tried to reproduce this issue, but I can't. So could you provide more details about errors, for example worksheet, screen shot etc.. That will help us reproduce and resolve your issue.

Monday, November 16, 2015 9:36 AM
• Hi,

This is problem here,

Monday, November 16, 2015 3:03 PM
• Base on your screen shot, I have reproduced this issue, This issue caused by that you have used the Protect method to protect a worksheet or workbook so that it cannot be modified.

So I suggeest that you could refer to below code:

```For Each ws In ActiveWorkbook.Sheets

ws.Unprotect
Set curRange = ws.Range("A1:B15")
For nRow = 2 To curRange.Rows.Count 'start at 2 -- assuming you have a header row
curRange.Cells(nRow, 2) = Feet(curRange.Cells(nRow, 1))
Next nRow
ws.Protect

Next ws
```

Wednesday, November 18, 2015 1:19 AM
• Hi,

Thank you.

Not working.

regards

Wednesday, November 18, 2015 3:43 PM
• >>>Not working.

For example sample codes, excel file, excel version etc., Effective information will help us reproduce and resolve your issue.