none
how to run a function from sub RRS feed

  • 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 Next

    regards

    Sunday, November 8, 2015 9:51 AM
  • Alt-F11 to go to the Visual Basic Editor

    from the menu:
    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 Explicit
    Function 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 Function

    regards


    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

    For more information, click here to refer about Range Object (Excel)

    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.

    Thanks for your understanding.

    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
    

    For more information, click here to refer about Worksheet.Protect Method (Excel)

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

    Thank you.

    Not working.

    regards

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

    Do you get errors? Could you provide more information about that it is not working?

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

    Thanks for your understanding.

    Tuesday, November 24, 2015 1:12 AM
  • Base on your screen shot, I just find #VALUE! error. Microsoft Excel may display the #VALUE! error if your formula includes cells that contain different data types. If error checking is enabled and you position the mouse pointer over the error indicator, the ScreenTip displays "A value used in the formula is of the wrong data type." You can typically fix this problem by making minor changes to your formula.

    For more information, click here to refer about Correct a #VALUE! error

    Wednesday, November 25, 2015 5:56 AM