none
HOW CAN CHANGE FUNCTION IN TO A VBA SUB RRS feed

  • Question

  • Hi,

    A function requires a formula to be entered in a cell , Like "=FEET(A1)^2" in B1 for function to get action of function FEET in squire in cell B1. How to get it done without a need to write "=FEET(A1)^2" but simply "=(A1)^2" to work instead.

    Can we change this function to a sub , fully functional ?

    Or a way to get it through a sub , like worksheet_calculate() .

    Or a way to run this function with in sub worksheet_calculate().

    regards


    Monday, November 9, 2015 3:37 PM

Answers

  • Use it as =Normalize(A2)^2.

    Option Explicit
    
    Public Function Normalize(ByVal AValue As Variant) As Variant
    
      Dim ParsedValue As Double
      Dim Result As Variant
      
      Result = AValue
      If TryParseFeet(AValue, ParsedValue) Then
        Result = ParsedValue
      End If
      
      Normalize = Result
      
      Debug.Print "AValue: '" & AValue; "', Result: " & Result
      
    End Function
    
    Private Function TryParseFeet(ByVal AValue As String, ByRef AParsedValue As Double) As Boolean
    
      On Local Error GoTo LocalError
    
      AParsedValue = 0
      TryParseFeet = False
      
      If IsFeetInch(AValue) Then
        AParsedValue = GetFeet(AValue) + GetInch(AValue) / 12
        TryParseFeet = True
      End If
      
      Exit Function
      
    LocalError:
    
    End Function
    
    Public Function GetFeet(ByVal AValue As String) As Long
    
      On Local Error GoTo LocalError
    
      Dim FeetPart As String
    
      FeetPart = Trim(Mid(AValue, 1, InStr(AValue, "'") - 1))
      GetFeet = CLng(FeetPart)
      
      Exit Function
      
    LocalError:
      GetFeet = 0
       
    End Function
    
    Public Function GetInch(ByVal AValue As String) As Long
    
      On Local Error GoTo LocalError
    
      Dim InchPart As String
    
      InchPart = Trim(Mid(AValue, InStr(AValue, "'") + 1))
      InchPart = Mid(InchPart, 1, Len(InchPart) - 1)
      
      GetInch = CLng(InchPart)
      
      Exit Function
      
    LocalError:
      GetInch = 0
       
    End Function
    
    Public Function IsFeetInch(ByVal AValue As String) As Boolean
    ' Use a waterproof test, like regex. This is just a simple, stupid example.
    
      Dim DoubleQuotes As Long
      Dim SingleQuotes As Long
    
      IsFeetInch = False
    
      AValue = Trim(Replace(AValue, " ", ""))
      
      DoubleQuotes = Len(AValue) - Len(Replace(AValue, """", ""))
      SingleQuotes = Len(AValue) - Len(Replace(AValue, "'", ""))
      
      If (DoubleQuotes > 1 Or SingleQuotes > 1) Or (DoubleQuotes + SingleQuotes = 0) Then
        Exit Function ' To few or too many quotes.
      End If
      
      DoubleQuotes = InStr(AValue, """")
      SingleQuotes = InStr(AValue, "'")
      
      If DoubleQuotes < SingleQuotes Then
        Exit Function ' " before '.
      End If
      
      IsFeetInch = True
      
    End Function
    

    The key methods are Normalize() and TryParseFeet(). The implementation of TryParseFeet() is up to you.

    Wednesday, November 25, 2015 10:01 AM
  • >>Can we go without "=function name (A1)^2" like "=A1^2" ?I mean no need to enter function name in formula ?<<

    No, it is impossible. If we don't enter the function name, the Excel doesn't know how the value should be calculated.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 27, 2015 8:14 AM
    Moderator

All replies

  • Magic?

    How should Excel know, which formula should be used, when you don't specify it?

    Monday, November 9, 2015 3:40 PM
  • Hi,

    Thank you,

    I mean by presence of particular entry in cell A1 eg. like 9'9'' or 7'7'', here ' and '' in current function. Excel can detect ' and '' in cell , value from which is calculated. No ?

    regards.

    Monday, November 9, 2015 3:59 PM
  • I'm not aware of any automatism in Excel, which allows to detect or transform values having SI or Imperial scale units into other ones.

    Maybe there are some plugins, but I don't know one.

    Monday, November 9, 2015 4:21 PM
  • Hello,

    You mean something like this:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
        Dim rg As Range, strFeet As String, strInch As String
        Dim strResult As String
        
        For Each rg In ActiveSheet.UsedRange
            If InStr(1, rg.Value, "'", vbTextCompare) > 1 Then
                strFeet = Mid(rg, 1, InStr(1, rg, "'", vbTextCompare) - 1)
                strInch = Replace(Mid(rg, InStr(1, rg, "'", vbTextCompare) + 1), "''", "")
                
                strResult = WorksheetFunction.Convert(strFeet, "m", "ft") + WorksheetFunction.Convert(strFeet, "m", "in")
                
                rg = CDbl(strResult)
            End If
        Next
        
    
    End Sub
    This function expects you to enter the measurements like x'y''

    instead of selectionChange event, you can choose any other.

    Hope it helps,

    Wouter

    Monday, November 9, 2015 4:28 PM
  • Thank you Wouter Defour ,

    No, This calculates in same cell automatically and changes value.

    regards

    Monday, November 9, 2015 4:38 PM
  • Thank you,

    I don't want plugins , they act as virus.

    Here is function by Andreas Killer , works fine . I want a complete automation.

    Function EvaluateFeetAndInch(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 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 End Function

    One more question , how to use  ALPHABETS like SECOND or TIGER or ASDFG etc instead ' or '' ? can we ?

    Monday, November 9, 2015 4:44 PM
  • Hi,

    >>How to get it done without a need to write "=FEET(A1)^2" but simply "=(A1)^2" to work instead. <<

    No, there is no way we can achieve the goal. Would you mind sharing why you need to do like this?

    >>Can we change this function to a sub , fully functional ?

    Or a way to get it through a sub , like worksheet_calculate() .

    Or a way to run this function with in sub worksheet_calculate().<<

    Did you mean that "FEET" is an function from VBA? What did you mean that changing the function to sub? As far as I know, the difference between function and sub is that the function return the value. And the Worksheet_calculate is an event occurs after the worksheet is recalculated, for the Worksheet object. It is different with the custom function or sub.

    Yes, we can run this function with in Worksheet calculate event. For example, we want to calculate the B1 value as you described in the original post, we can use code below:

    Private Sub Worksheet_Calculate()
    Me.Range("B1").Value = feet(Me.Range("A1"))
    End Sub
    

    Then when the calculate event for the sheet was fired, it we recalculate the B1's value.

    In addition, here are some helpful links for you leaning Excel developing:
    How do I... (Excel VBA reference)

    Concepts (Excel VBA reference)

    Object model (Excel VBA reference)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 10, 2015 2:38 AM
    Moderator
  • Why not add another function called SqFt.  then you can do =SqFt(A1)

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

    Function SqFt(pValue)

       SqFt = FEET(pValue)^2

    End Function

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


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

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

    This only changes name of Function.

    regards

    Tuesday, November 10, 2015 1:41 PM
  • I don't want plugins , they act as virus.

    Here is function by Andreas Killer , works fine . I want a complete automation.

    One more question , how to use  ALPHABETS like SECOND or TIGER or ASDFG etc instead ' or '' ? can we ?

    1. quote:

    Sorry, but that is nonsense. Any file that contains code can harm your PC.

    3. quote:

    replace the words at the start of the function, same place as the other Replace-Code:

      What = Replace$(What, "Tiger", "'") 'Use Tiger as '
      What = Replace$(What, "Lion", """") 'Use Lion as "

    2. quote:

    Excel can only calculate with numbers, it is not possible to change that behavior.

    So you can use the UDF as suggested, that is the save way.

    The only other way is to change an input like 9'9" to 9.75 automatically, for that you can use the Worksheet_Change event routine.

    But that is critical, because
    a) you have to specify a range of cells in where this conversation should be done and
    b) you have to check if a cell does really contain a length input in your format and
    c) you are not able anymore to write 9'9" into any of this cells.

    Below is a sample for the whole sheet

    Andreas.

    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Value, i As Long
      If Target.Count > 1 Then Exit Sub
      On Error GoTo ExitPoint
      Value = Target.Value
      If InStr(Value, "'") = 0 And InStr(Value, """") = 0 Then Exit Sub
      Value = Replace(Value, "'", "")
      Value = Replace(Value, """", "")
      If Not IsNumeric(Value) Then Exit Sub
      Application.EnableEvents = False
      Target.Value = EvaluateFeetAndInch(Target.Value)
    ExitPoint:
      Application.EnableEvents = True
    End Sub


    Tuesday, November 10, 2015 3:03 PM
  • Thank you Andreas Killer ,

    My opinion about plugin is due to previous experience , I had to reinstall everything.

    This is what I want . But a little or huge more. I want it to be visible as I type and behave like it does while using this code.

    e.g. Visible as 9'9'' and behave as 9.75 while calculations.

    It can solve many problems like AM/PM , Fah/Cel etc.

    Here I want for Foot/Meter.

    regards

    Wednesday, November 11, 2015 3:36 AM
  • I want it to be visible as I type and behave like it does while using this code.

    e.g. Visible as 9'9'' and behave as 9.75 while calculations.


    As I said: That is not possible, no chance.

    Andreas.

    Wednesday, November 11, 2015 8:01 AM
  • Thank you ,

    regards

    Wednesday, November 11, 2015 9:25 AM
  • What I would suggest, then, is to convert the entry to decimal to show in the cell and make the 9'9" a note.  You can make notes display ... so you can actually have both ;) ~

    alternately, put a column next to it (or not) with decimal values, which can be calculated (and not even necessarily visible).  That way, equations based on the value would be much easier without having to calculate it all the time.


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



    Wednesday, November 11, 2015 5:18 PM
  • Thank you Fei Xue ,

    It works fine for a range B1 , or rather cell B1. Can it be done anywhere in worksheet , if the cell from which value are taken for calculation has value like 8'9'' or 4'5'' or 7'2'' etc.

    Thanks

    Thursday, November 12, 2015 1:26 PM
  • See my code above. If you adapt it a little it will calculate what you want
    Thursday, November 12, 2015 4:19 PM
  • Can it be like this,

    Private Sub Worksheet_Calculate()
       If Cells(x).Value = "'" Or "''" Then ActiveCell.Value = Feet(Me.Range(x))
    End Sub

    Feet is function here.

    regards



    Thursday, November 12, 2015 4:57 PM
  • Can it be like this,

    Private Sub Worksheet_Calculate()
       If Cells(x).Value = "'" Or "''" Then ActiveCell.Value = Feet(Me.Range(x))
    End Sub

    Feet is function here.

    regards



    Yes, we can detect the every value in the used range and replace the value with our own requirement. However, this may take a long time when you have a larger worksheet to calculate.

    Here is the code for your reference:

    Private Sub Worksheet_Calculate()
     Dim strPattern As String: strPattern = "\d+'\d+''"
      Dim regEx As New RegExp
    
     With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With
    
    
    For Each acell In UsedRange
    
    If regEx.Test(acell.Value) Then
        
        acell.Value = Feet(acell.Value)
    
    End If
    
    Next acell
    End Sub
    
    
    

    To run the code above, we need to add the "Microsoft VBScript Regular Expressions 5.5" which match the cell we want to use the custom procedure for reference via VBE->Tools->References.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 13, 2015 5:39 AM
    Moderator
  • Hi ,

    Thank you Fei Xue ,

    It is working nice. It modifies the value of original cell too . How to stop it ?

    regards

    Friday, November 13, 2015 11:39 AM
  • Hi,

    This event would only occurred when the worksheet was calculated. If you want this event only occurred when you wanted, you can declare an flag in the worksheet module. And modify the code based on the flag.

    Here is an demo code for your reference:

    Dim isEnable As Boolean
    
    
    Sub Worksheet_Calculate()
    If isEnable Then
    'add the code here want to run
    
    End If
    
    End Sub
    
    Sub changeStatus()
    If isEnable Then
    isEnable = False
    Else
    isEnable = True
    End If
    
    End Sub
    

    In addition, I suggest that you reopen a new thread if you a problem about Excel developing so that other communities can read the post more easily and you would get more effective response.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 16, 2015 7:10 AM
    Moderator
  • Hi Fei Xue ,

    I probably did not put problem properly . If I have 9'9'' in A1 and I insert "=A1^2" in B1 . It change value in A1 from 9'9'' to 9.75 and then calculates in B1 with correct result . I want the value back 9'9'' in A1 and not 9.75 . How can you do this ? Can an undone possible there in A1 or similar cell in worksheet ?

    I asked here again because your code worked to great extent . I have put elsewhere similar problem . No success there . 

    regards


    Monday, November 16, 2015 3:13 PM
  • Hi,

    Thanks for the detail information for this issue.

    >>If I have 9'9'' in A1 and I insert "=A1^2" in B1 . It change value in A1 from 9'9'' to 9.75 and then calculates in B1 with correct result . I want the value back 9'9'' in A1 and not 9.75 . How can you do this ? Can an undone possible there in A1 or similar cell in worksheet ?<<

    Yes, we can log the cell we have calculated and write the own function to change the value back. However, if you change the value back to '9'9', the value B1 will be invalid since the formula is incorrect with the parameter.

    Here is the sample code for your reference:

    Dim feetCells As Collection
    
    
    
    Private Sub Worksheet_Calculate()
    Set feetCells = New Collection
    
     Dim strPattern As String: strPattern = "\d+'\d+''"
      Dim regEx As New RegExp
    
     With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With
    
    
    For Each acell In UsedRange
    
    If regEx.Test(acell.Value) Then
        
        acell.Value = feet(acell.Value)
        feetCells.Add acell
    End If
    
    Next acell
    End Sub
    
    Function feet(str As String)
    feet = 5
    End Function
    
    Function unFeet(val As Integer)
    unFeet = "9'9''"
    End Function
    
    Sub unDoFeet()
    For i = 1 To feetCells.Count
    
    feetCells(i).Value = unFeet(feetCells(i).Value)
    Next i
    End Sub
    
    

    You can modify the "feet" and "unFeet" sub based on your requirement and run "unDoFeet" sub would change the value back.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, November 17, 2015 9:48 AM
    Moderator
  • Hi Fei Xue ,

    Thank you ,

    Now it seems that is not possible .

    regards

    Tuesday, November 17, 2015 3:38 PM
  • Hi,

    >>Now it seems that is not possible .<<

    If I misunderstood please feel free to let me know. Based on my understanding, it should be possible as the preview post.

    Did you try the code above and have any problem?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 18, 2015 7:41 AM
    Moderator
  • Hi Fei Xue,

    Thank you,

    Private Sub Worksheet_Calculate() Dim strPattern As String: strPattern = "\d+'\d+''" Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .Pattern = strPattern End With For Each acell In UsedRange If regEx.Test(acell.Value) Then acell.Value = Feet(acell.Value) End If Next acell End Sub

    This Works fine. Only change needed is that , the source value should remain like 9'9'' or 8'8'' or 5'7'' and not as 9.75 or 8.66 or 5.58 etc .

    is it possible.

    regards

    Wednesday, November 18, 2015 3:28 PM
  • btw, have you considered using a hidden column for the conversion when needed. And using this column in your formula?

    Another approach would be making the conversion function more flexible, so that works correctly with all kinds of values. Then you can use it always in your formulas.

    Wednesday, November 18, 2015 3:42 PM
  • Hi,

    Thank you,

    There are better option than the first suggestion .

    The later suggestion is what , I want and unable to do .

    regards

    Wednesday, November 18, 2015 3:47 PM
  • Hi Fei Xue,

    Thank you,

    Private Sub Worksheet_Calculate() Dim strPattern As String: strPattern = "\d+'\d+''" Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .Pattern = strPattern End With For Each acell In UsedRange If regEx.Test(acell.Value) Then acell.Value = Feet(acell.Value) End If Next acell End Sub

    This Works fine. Only change needed is that , the source value should remain like 9'9'' or 8'8'' or 5'7'' and not as 9.75 or 8.66 or 5.58 etc .

    is it possible.

    regards

    Yes, if you want to calculate the cell which based on the cell contained 9'9'' or 8'8'' or 5'7'' etc, we just need to modify the active cell to which cell we want to calculate.

    For example, if the target cell always on the right of the cell which contained 9'9'' or 8'8'' or 5'7'' etc. A1=9'99' and we need to calculate the value on B1, we can modify the code like below:

    Dim strPattern As String: strPattern = "\d+'\d+''"
      Dim regEx As New RegExp
    
     With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With
    
    
    For Each acell In UsedRange
    
    If regEx.test(acell.Value) Then
        
        acell.Offset(0, 1).Value = Feet(acell.Value)
    
    End If
    
    Next acell

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 24, 2015 5:47 AM
    Moderator
  • Hi Fei Xue ,

    Thank you,

    acell.Offset(0, 1).Value = Feet(acell.Value)

    Here it should be like , "If source cell has 8' or 9'' then the value should be considered as 8 for 8' and .75 for 9'', or 5 for 5' and .5 for 6'' etc while calculation . No mediate cell to be used . Then only be used in formula ."

    regards



    Tuesday, November 24, 2015 2:58 PM
  • Hi,

    >>Here it should be like , "If source cell has 8' or 9'' then the value should be considered as 8 for ' and .75 for ''. Then only be used in formula ." <<

    Did you mean that the calculation replace the "'" with 8 and "''" with ".75" then return the value? If I understood correctly, we can use code below to Evaluate function to return numbers from string. Here is the code for your reference:

    Function Feet(a As String)
    Feet = Evaluate(Replace(Replace(a, "''", ".75"), "'", "8"))
    
    End Function

    And if also suggest that you reopen a new thread in VBA forum if you have any issues about VBA developing.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 25, 2015 8:44 AM
    Moderator
  • Use it as =Normalize(A2)^2.

    Option Explicit
    
    Public Function Normalize(ByVal AValue As Variant) As Variant
    
      Dim ParsedValue As Double
      Dim Result As Variant
      
      Result = AValue
      If TryParseFeet(AValue, ParsedValue) Then
        Result = ParsedValue
      End If
      
      Normalize = Result
      
      Debug.Print "AValue: '" & AValue; "', Result: " & Result
      
    End Function
    
    Private Function TryParseFeet(ByVal AValue As String, ByRef AParsedValue As Double) As Boolean
    
      On Local Error GoTo LocalError
    
      AParsedValue = 0
      TryParseFeet = False
      
      If IsFeetInch(AValue) Then
        AParsedValue = GetFeet(AValue) + GetInch(AValue) / 12
        TryParseFeet = True
      End If
      
      Exit Function
      
    LocalError:
    
    End Function
    
    Public Function GetFeet(ByVal AValue As String) As Long
    
      On Local Error GoTo LocalError
    
      Dim FeetPart As String
    
      FeetPart = Trim(Mid(AValue, 1, InStr(AValue, "'") - 1))
      GetFeet = CLng(FeetPart)
      
      Exit Function
      
    LocalError:
      GetFeet = 0
       
    End Function
    
    Public Function GetInch(ByVal AValue As String) As Long
    
      On Local Error GoTo LocalError
    
      Dim InchPart As String
    
      InchPart = Trim(Mid(AValue, InStr(AValue, "'") + 1))
      InchPart = Mid(InchPart, 1, Len(InchPart) - 1)
      
      GetInch = CLng(InchPart)
      
      Exit Function
      
    LocalError:
      GetInch = 0
       
    End Function
    
    Public Function IsFeetInch(ByVal AValue As String) As Boolean
    ' Use a waterproof test, like regex. This is just a simple, stupid example.
    
      Dim DoubleQuotes As Long
      Dim SingleQuotes As Long
    
      IsFeetInch = False
    
      AValue = Trim(Replace(AValue, " ", ""))
      
      DoubleQuotes = Len(AValue) - Len(Replace(AValue, """", ""))
      SingleQuotes = Len(AValue) - Len(Replace(AValue, "'", ""))
      
      If (DoubleQuotes > 1 Or SingleQuotes > 1) Or (DoubleQuotes + SingleQuotes = 0) Then
        Exit Function ' To few or too many quotes.
      End If
      
      DoubleQuotes = InStr(AValue, """")
      SingleQuotes = InStr(AValue, "'")
      
      If DoubleQuotes < SingleQuotes Then
        Exit Function ' " before '.
      End If
      
      IsFeetInch = True
      
    End Function
    

    The key methods are Normalize() and TryParseFeet(). The implementation of TryParseFeet() is up to you.

    Wednesday, November 25, 2015 10:01 AM
  • Hi Stefan Hoffmann ,

    Thank you .

    This function work in the same way

    Function EvaluateFeetAndInch(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 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 End Function


    Can we go without "=function name (A1)^2" like "=A1^2" ?

    I mean no need to enter function name in formula ?

    regards


    Wednesday, November 25, 2015 10:31 AM
  • >>Can we go without "=function name (A1)^2" like "=A1^2" ?I mean no need to enter function name in formula ?<<

    No, it is impossible. If we don't enter the function name, the Excel doesn't know how the value should be calculated.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 27, 2015 8:14 AM
    Moderator
  • Thank you Fei Xue ,

    regards.

    Friday, November 27, 2015 1:21 PM
  • No, Excel cannot do this.

    Either you go the event way, and check each cell and formula involved and change that which is pretty complex.

    Or you go the easy way and use always a function which normalizes all numeric values to the same base.

    Also it is not the same as your function. My function tests whether it contains feet/inch and does the conversion. Otherwise the original value is returned.

    When you apply your function (=EvaluateFeetAndInch(A2)^2.) on the cell value 3, then the result is 0.

    When you apply my function (=Normalize(A2)^2.) on the cell value3, then the result is 9

    So the difference is: You use always my approach, then your formulas will return the correct value. Independently whether a normal number was entered or a feet/inch string. 
    Friday, November 27, 2015 2:07 PM
  • Hi Stefan Hoffmann,

    Thank you,

    >>When you apply your function (=EvaluateFeetAndInch(A2)^2.) on the cell value 3, then the result is 0.

    I could not produce this , can you please tell me details please .

    regards.

    Friday, November 27, 2015 4:50 PM
  • Hi,

    You can set 3 on A2 and set the formula using "=EvaluateFeetAndInch(A2)^2" on A1. If you using the function you provide in preview post, the result of A1 should be 0. However if using the function provide by Stevan, you would get 9.

    That the difference in the function provide by you two. We can customize the function as we wanted. Since the post is too long, it is not easy for others read. I suggest that you reopen a new thread, if you have other problem about Excel developing.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 1, 2015 1:08 AM
    Moderator
  • Thank you Fei Xue,

    =Normalize(A2)^2 gives #VALUE! error when A2 value is 3' or 3''. It works when value is 3 . =TryParseFeet(C2)^2 also gives same error.

    regards


    Tuesday, December 1, 2015 4:33 PM
  • Hi,

    Thanks for the detail information for this issue.

    Since this thread is too long and the original issue was resolved, to get more effective response, I suggest that you reopen a new thread in this forum about Excel developing.

    And if you have any problem about VBA issues, VBA forum is the correct forum.

    Thanks for your understanding.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 2, 2015 6:56 AM
    Moderator