none
Problems with using the active cell functionality RRS feed

  • Question

  • Environment:

    Windows 8.1

    Excel 2013

    Task being sought to be achieved:

    I wish to set a value in the current cell, which is in column O, by looking at the 'properties' of the cell in column A of the same row. 

    If the cell in column A has a border to it, then I wish the function to return a value of 2;

    If the cell in column A is bolded, then I wish the function to return a value of 10;

    If both situations apply, then I wish the function to return a value of 12.

    If neither situation applies, then I wish the function to return a value of 0.

    Code written:

    xlsx cell value - Call Statement: =Cousins_Estimate((ROW()))

    xlsm code -

    Option Explicit

    Public Function Cousins_Estimate(Row_Number As Long) As Long

    Application.Volatile

    Dim fRow_Number

    Dim Cousins_Calculation As Long

    Dim ShtName As String

    Dim Border_Found As String

    Dim Bold_Found As String

    fRow_Number = Row_Number

    Cousins_Calculation = 0

    ShtName = Application.Caller.Parent.Name

    Worksheets(ShtName).Activate

    Worksheets(ShtName).Range(Cells(fRow_Number, 1)).Activate

    Border_Found = "No"
    Bold_Found = "No"

    If ActiveCell.Borders(xlEdgeBottom).LineStyle = xlLineStyleNone Then

      Cousins_Calculation = 0

    Else

      Cousins_Calculation = 2
      Border_Found = "Yes"

    End If

    If ActiveCell.Font.Bold = True Then

      Cousins_Calculation = Cousins_Calculation + 10
      Bold_Found = "Yes"

    Else

      Cousins_Calculation = Cousins_Calculation

    End If

    Cousins_Estimate = Cousins_Calculation

    End Function

    Actions taken - initial thoughts:

    Some of the above code is there for my debugging only, such as the Bold_Found stuff.  I have inserted many breakpoints and debugging runs seem to proceed happily up to the line that I have bolded, i.e. Worksheets(ShtName).Range(Cells(fRow_Number, 1)).Activate

    I am of the understanding, from many other threads on the web, that that statement should make the cell in column A on the row(which has been passed through the function call) to be the ActiveCell, which I then seek to use in the following code.

    Debug runs appear to fall over at this point, runs without debug return a #VALUE! error.

    I should be most grateful for all input regarding what is clearly some misunderstanding on my part.  I need to call the function thousands of times in the workbook, so it is crucial that I get this solved.

    With thanks in anticipation.

    Warm regards

    Philip, Bendigo
    Victoria


    Philip Hunt Flora Hill, Bendigo, Victoria Webmaster www.howandwhywonderbooks.info (Phase 1 operative)

    Monday, September 14, 2015 7:15 AM

Answers

  • You cannot activate another cell in a function that is called from a cell formula. Instead, use a variable of type Range that refers to the cell in the same row in the first column:

    Public Function Cousins_Estimate() As Long
        Dim cel As Range
        Application.Volatile
        Set cel = Application.Caller
        Set cel = cel.Offset(0, 1 - cel.Column)
        If cel.Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
            Cousins_Estimate = 2
        End If
        If ActiveCell.Font.Bold = True Then
            Cousins_Estimate = Cousins_Estimate + 10
        End If
    End Function

    Call like this in a cell formula:

    =Cousins_Estimate()


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 14, 2015 2:45 PM

All replies

  • You cannot activate another cell in a function that is called from a cell formula. Instead, use a variable of type Range that refers to the cell in the same row in the first column:

    Public Function Cousins_Estimate() As Long
        Dim cel As Range
        Application.Volatile
        Set cel = Application.Caller
        Set cel = cel.Offset(0, 1 - cel.Column)
        If cel.Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
            Cousins_Estimate = 2
        End If
        If ActiveCell.Font.Bold = True Then
            Cousins_Estimate = Cousins_Estimate + 10
        End If
    End Function

    Call like this in a cell formula:

    =Cousins_Estimate()


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 14, 2015 2:45 PM
  • Dear Hans

    Thank you so very much.

    Warmest regards

    Philip


    Philip Hunt Flora Hill, Bendigo, Victoria Webmaster www.howandwhywonderbooks.info (Phase 1 operative)

    Tuesday, September 15, 2015 3:20 AM