Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writing to an excel cell (VBA)

    I would have thought that this was simple.

    In the course of writing a function for Excel, where I need to create a counter and increment it and then write the value of the counter to the local cell, I have driven into the fog.

    I have created a cell called "Counter", where an integer is stored, which needs to be incemented.

    I can read the counter value and write it into the calling cell.

    What I cannot do is write a value to the Counter cell.

    I can read the counter with MyCell = Worksheets("Sheet1").Range("Counter").Value, but if I try to write a value back with something like Worksheets("Sheet1").Range("Counter").Value = MyCell + 1 it falls over.

    What do I need to do?

    Any help appreciated

    Godfrey

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Writing to an excel cell (VBA)

    How is it falling over? What is the error you are getting?

    It looks to me like you have not defined what type of data the variable MyCell can contain. If it is a string and you add 1 to it then I would certainly expect an error.

    If you add a line in front to dimension the variable does this work?
    Dim MyCell as Double

    Alternatively can you set the type of data you are expecting in the first line with
    MyCell = Val(Worksheets("Sheet1").Range("Counter").Value)
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to an excel cell (VBA)

    Thanks for your response Andrew.

    The function that I have put in the active cell is this:

    Function ThingyCounter(MyConstant As Integer)
    MyConstant = MyConstant + 1 'Increment the counter
    Worksheets("Sheet1").Range("Counter").Value = MyConstant 'Write the new value to the counter
    ThingyCounter = MyConstant 'Return the new counter value to the cell
    End Function

    It "falls over" in the second line [Worksheets ...] with the error message
    "A value used in the formula is of the wrong data type"
    If line two is commented out the function enters an incremented value in the active cell.

    Both the active cell and the cell named "Counter" are set to number, no decimals.

    Godfrey

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to an excel cell (VBA)

    A function that is executed from a formula in a cell can not do any operation that directly modifies a worksheet, it can only return a value to the formula that called it.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to an excel cell (VBA)

    Thanks Legare:

    Is this the "Law of the Medes and Persians" or the law brought down from the mountain , or just something that is undocumented and learned from experience? (Sort of like Common Law?)

    I feared that something like this might be going on but the error message didn't even come close.

    is there a counter function in built in Excel VBA that I have just not found?

    Godfrey <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to an excel cell (VBA)

    Allowing a Function to modify a cell when the function was called from a formula could reek all kinds of havoc. For example, if the function were to modify a cell that the function was dependant on, it would put the worksheet into a never ending loop. Or, if the function replaced the formula that called the function, it would cause all kinds of mahem. This is documented in the help files, but I don't remember where at the moment.

    What are you trying to count? If you give us a description of what you are trying to do, someone can probably come up with a way to do it.
    Legare Coleman

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Writing to an excel cell (VBA)

    From the direction Legare is taking it appears you have written the function as a wrapper. I can understand why it isn't going to work in that case because it would spawn a loop which would never end (every time it updates itself, it updates itself)

    I was expecting you were doing the incrementation by using a macro to call the function in the following way
    <pre>Sub Prognosis()
    Dim MyCell As Double
    'check what the named range actually includes (it should be one cell)
    Worksheets("Sheet1").Range("Counter").Select
    MyCell = Worksheets("Sheet1").Range("Counter").Value
    Debug.Print MyCell
    ThingyCounter MyCell
    End Sub
    Function ThingyCounter(MyConstant As Double) As Double
    MyConstant = MyConstant + 1 'Increment the counter
    Debug.Print MyConstant
    Worksheets("Sheet1").Range("Counter").Value = MyConstant 'Write new value to cell
    ThingyCounter = MyConstant 'Return the new counter value to the cell
    End Function</pre>

    This sub can be run to increment the value in the cell named Counter.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #8
    New Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to an excel cell (VBA)

    Legare:

    Here's what I'm counting. The function will be in the 'test is true' section of a condition if statement. If an adjacent cellis not blank, the active cell will contain a number as a label (1, 2, 3, ...). If it is blank, it will not have such a label. Because the cells to be labeled are not necessarily adjacent, autofil can not be used to write the label. Hence the need to write the function.

    Godfrey

  9. #9
    New Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to an excel cell (VBA)

    Andrew:

    I would not have thought that this would have led to a recursive type of error. It essentially involves looking up a value and writing it to the active cell and then updating the counter cell. Finish. There is nothing that could take the processing through the loop a second time.

    I will try your suggestion over the weekend and let you know if it does the trick.

    Many thanks

    Godfrey

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to an excel cell (VBA)

    Is the sequence of the incremented number of importance?

    If not, consider using this:

    Option Explicit

    <pre>Dim lCount As Long

    Function Increment(bReset As Boolean)
    Application.Volatile
    If bReset Then
    lCount = 1
    Else
    lCount = lCount + 1
    End If
    Increment = lCount
    End Function
    </pre>

    (normal module)

    Now in the FIRST cell that calls the function (I guess the top-left) use Increment(TRUE). In all others use Increment(FALSE).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to an excel cell (VBA)

    This sounds like something that would have to be done using the Worksheet Change event routine, but I am not 100% sure I completely understand what you want to do. To write the routine, I would also need to know what columns are involved (where the label goes and what column is being tested for blank), and if there are any rows that would not be included (like header rows). Could you upload an example workbook that shows what you are trying to do?
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •