Use of a variable as a parameter fro Range property


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: Use of a variable as a parameter fro Range property

  1. #1
    makkineni Guest

    Use of a variable as a parameter fro Range property



    I am trying to bring some data from vb to excel spread sheet.
    The cell into which i'll put the data is dynamic.
    So i am trying to use a variable which tells the position instead of the
    exact cell name.
    For example:
    wksExcel.Range("A1").Value = "test"
    Instead of "A1" i am using a variable which holds the value "A1".
    wksExcel.Range(pos).Value = "test", where pos is a string variable.
    Are we not supposed to use variable as parameter for range.
    But, this gives me a runtime error as "Application defined or object defined
    error".
    If somebody knows anything about this, please respond.
    thanks.



  2. #2
    Antti Guest

    Re: Use of a variable as a parameter fro Range property



    When you are calling Range property from a VB program you must use a variable
    of Variant type instead of String type as parameter.

    Depending on your needs you might also consider using Cells property instead
    of Range. Cells property takes two parameters row number and column number.
    Again if you are calling Cells property from VB you must use variables of
    Variant type as parameters.

    Antti

    "makkineni" <m_akkineni@hotmail.com> wrote:
    >
    >
    >I am trying to bring some data from vb to excel spread sheet.
    >The cell into which i'll put the data is dynamic.
    >So i am trying to use a variable which tells the position instead of the
    >exact cell name.
    >For example:
    >wksExcel.Range("A1").Value = "test"
    >Instead of "A1" i am using a variable which holds the value "A1".
    >wksExcel.Range(pos).Value = "test", where pos is a string variable.
    >Are we not supposed to use variable as parameter for range.
    >But, this gives me a runtime error as "Application defined or object defined
    >error".
    >If somebody knows anything about this, please respond.
    >thanks.
    >
    >



  3. #3
    Paul Clement Guest

    Re: Use of a variable as a parameter fro Range property

    On 3 Oct 2001 13:00:50 -0700, "makkineni" <m_akkineni@hotmail.com> wrote:



    I am trying to bring some data from vb to excel spread sheet.
    The cell into which i'll put the data is dynamic.
    So i am trying to use a variable which tells the position instead of the
    exact cell name.
    For example:
    wksExcel.Range("A1").Value = "test"
    Instead of "A1" i am using a variable which holds the value "A1".
    wksExcel.Range(pos).Value = "test", where pos is a string variable.
    Are we not supposed to use variable as parameter for range.
    But, this gives me a runtime error as "Application defined or object defined
    error".
    If somebody knows anything about this, please respond.
    thanks.


    The syntax looks to be fine so I would guess the problem is with the wksExcel object. Is this a
    worksheet? Perhaps a little more code might help us to troubleshoot the problem.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  4. #4
    makkineni Guest

    Re: Use of a variable as a parameter fro Range property


    Please find the code i used. It is a worksheet object.
    Dim cols As Integer
    Dim rows As Integer
    cols = FG.cols
    rows = FG.rows
    '*** HERE **
    Dim appExcel As Object
    Dim wkbExcel As Object
    Dim wksExcel As Object
    Set appExcel = CreateObject("Excel.Application")
    Set wkbExcel = appExcel.Workbooks.Add()
    Set wksExcel = wkbExcel.Worksheets.Add
    Dim i As Variant
    Dim j As Variant
    Dim pos As Variant
    For i = Asc("A") To Asc("a")
    For j = 1 To cols
    pos = Chr(i) + Str(j)

    On Error GoTo ShowName_Err
    wksExcel.Range(pos).Value = "test"



    Next
    Next

    appExcel.Visible = True

    -makkineni


    Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >On 3 Oct 2001 13:00:50 -0700, "makkineni" <m_akkineni@hotmail.com> wrote:
    >
    >
    >
    > I am trying to bring some data from vb to excel spread sheet.
    > The cell into which i'll put the data is dynamic.
    > So i am trying to use a variable which tells the position instead of the
    > exact cell name.
    > For example:
    > wksExcel.Range("A1").Value = "test"
    > Instead of "A1" i am using a variable which holds the value "A1".
    > wksExcel.Range(pos).Value = "test", where pos is a string variable.
    > Are we not supposed to use variable as parameter for range.
    > But, this gives me a runtime error as "Application defined or object defined
    > error".
    > If somebody knows anything about this, please respond.
    > thanks.
    >
    >
    >The syntax looks to be fine so I would guess the problem is with the wksExcel

    object.
    >Is this a
    >worksheet? Perhaps a little more code might help us to troubleshoot the

    problem.
    >
    >
    >Paul ~~~ pclement@ameritech.net
    >Microsoft MVP (Visual Basic)



  5. #5
    makkineni Guest

    Re: Use of a variable as a parameter fro Range property


    When i made the variable as variant type i get the following error.
    "Wrong number of arguments or invalid property assignment".


    "Antti" <antti_komonen@hotmail.com> wrote:
    >
    >
    >When you are calling Range property from a VB program you must use a variable
    >of Variant type instead of String type as parameter.
    >
    >Depending on your needs you might also consider using Cells property instead
    >of Range. Cells property takes two parameters row number and column number.
    >Again if you are calling Cells property from VB you must use variables of
    >Variant type as parameters.
    >
    >Antti
    >
    >"makkineni" <m_akkineni@hotmail.com> wrote:
    >>
    >>
    >>I am trying to bring some data from vb to excel spread sheet.
    >>The cell into which i'll put the data is dynamic.
    >>So i am trying to use a variable which tells the position instead of the
    >>exact cell name.
    >>For example:
    >>wksExcel.Range("A1").Value = "test"
    >>Instead of "A1" i am using a variable which holds the value "A1".
    >>wksExcel.Range(pos).Value = "test", where pos is a string variable.
    >>Are we not supposed to use variable as parameter for range.
    >>But, this gives me a runtime error as "Application defined or object defined
    >>error".
    >>If somebody knows anything about this, please respond.
    >>thanks.
    >>
    >>

    >



  6. #6
    Paul Clement Guest

    Re: Use of a variable as a parameter fro Range property

    On 4 Oct 2001 08:27:02 -0700, "makkineni" <m_akkineni@hotmail.com> wrote:


    Please find the code i used. It is a worksheet object.
    Dim cols As Integer
    Dim rows As Integer
    cols = FG.cols
    rows = FG.rows
    '*** HERE **
    Dim appExcel As Object
    Dim wkbExcel As Object
    Dim wksExcel As Object
    Set appExcel = CreateObject("Excel.Application")
    Set wkbExcel = appExcel.Workbooks.Add()
    Set wksExcel = wkbExcel.Worksheets.Add
    Dim i As Variant
    Dim j As Variant
    Dim pos As Variant
    For i = Asc("A") To Asc("a")
    For j = 1 To cols
    pos = Chr(i) + Str(j)

    Replace Str with CStr. You're picking up a leading space when you use Str for the plus sign (which
    is not present but implied) so the result of the pos variable is "A 1". I would also recommend using
    the concatenation character instead of the "+" sign:

    pos = Chr(i) & CStr(j)


    On Error GoTo ShowName_Err
    wksExcel.Range(pos).Value = "test"



    Next
    Next

    appExcel.Visible = True


    In addition, there are characters in the ANSI character set between "A" and "a" that are not
    alphabetic so you will need to skip those.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  7. #7
    Antti Guest

    Re: Use of a variable as a parameter fro Range property


    Because of the problems that Paul pointed out you would probably want to use
    Cells property instead of Range.

    for i = 1 to rows
    for j = 1 to cols
    wksExcel.cells(i,j).value="test"
    next j
    next i

    Antti


    Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >On 4 Oct 2001 08:27:02 -0700, "makkineni" <m_akkineni@hotmail.com> wrote:
    >
    >
    > Please find the code i used. It is a worksheet object.
    > Dim cols As Integer
    > Dim rows As Integer
    > cols = FG.cols
    > rows = FG.rows
    > '*** HERE **
    > Dim appExcel As Object
    > Dim wkbExcel As Object
    > Dim wksExcel As Object
    > Set appExcel = CreateObject("Excel.Application")
    > Set wkbExcel = appExcel.Workbooks.Add()
    > Set wksExcel = wkbExcel.Worksheets.Add
    > Dim i As Variant
    > Dim j As Variant
    > Dim pos As Variant
    > For i = Asc("A") To Asc("a")
    > For j = 1 To cols
    > pos = Chr(i) + Str(j)
    >
    >Replace Str with CStr. You're picking up a leading space when you use Str

    for the plus
    >sign (which
    >is not present but implied) so the result of the pos variable is "A 1".

    I would also
    >recommend using
    >the concatenation character instead of the "+" sign:
    >
    > pos = Chr(i) & CStr(j)
    >
    >
    > On Error GoTo ShowName_Err
    > wksExcel.Range(pos).Value = "test"
    >
    >
    >
    > Next
    > Next
    >
    > appExcel.Visible = True
    >
    >
    >In addition, there are characters in the ANSI character set between "A"

    and "a" that
    >are not
    >alphabetic so you will need to skip those.
    >
    >
    >Paul ~~~ pclement@ameritech.net
    >Microsoft MVP (Visual Basic)



  8. #8
    Dmitri Guest

    Re: Use of a variable as a parameter fro Range property


    Guys, I have the same problem as Makkineni, except that it cannot be solved
    using the solutions u posted here. OK, I want a comment to be inserted at
    the end of a large list of items (600+). And because insert comment option
    only works with range as far as I know, I have to put that variable there,
    so that the comment is put after the last item. So here's the code I use:

    Private Sub TextBox4_Change()
    AmountOfItems = Worksheets(1).Cells(4, 1) + 7 <--variable of the cell where
    the comment should be inserted<--
    Worksheets(1).Select
    Range("AmountOfItems").AddComment
    Range("AmountOfItems").Comment.Visible = False
    Range("AmountOfItems").Comment.Text Text:="unit: piece ""
    Range("AmountOfItems").Select
    End Sub

    Any help will be appreciated

    With respect,
    Dmitri

  9. #9
    Antti Guest

    Re: Use of a variable as a parameter fro Range property


    There are couple of problems with your code sample:

    Variable AmountOfItems gets whatever value is in cell A4 of first worksheet
    in active workbook plus 7. For example if cell A4 is empty AmountOfItems
    equals 7. I don't know what you are trying to do here but this does not seem
    right.

    Range("AmountOfItems").AddComment means you are trying to add comment to
    a named range. If named range "AmountOfItems" does not exist in active workbook
    this should result in error.

    In general if you are using Excel from within a VB application I would advice
    you to first get the code working in Excel and only after that export it
    to your VB application. Debugging is much easier that way.

    Antti

    "Dmitri" <dimiok_1@yahoo.com> wrote:
    >
    >Guys, I have the same problem as Makkineni, except that it cannot be solved
    >using the solutions u posted here. OK, I want a comment to be inserted at
    >the end of a large list of items (600+). And because insert comment option
    >only works with range as far as I know, I have to put that variable there,
    >so that the comment is put after the last item. So here's the code I use:
    >
    >Private Sub TextBox4_Change()
    >AmountOfItems = Worksheets(1).Cells(4, 1) + 7 <--variable of the cell where
    >the comment should be inserted<--
    >Worksheets(1).Select
    > Range("AmountOfItems").AddComment
    > Range("AmountOfItems").Comment.Visible = False
    > Range("AmountOfItems").Comment.Text Text:="unit: piece ""
    > Range("AmountOfItems").Select
    >End Sub
    >
    >Any help will be appreciated
    >
    >With respect,
    >Dmitri



  10. #10
    Dmitri Guest

    Re: Use of a variable as a parameter fro Range property


    Hi, thanks for you response Antti.
    Ok, I think you a little misunderstood my code. So, I'm gonna try and explain
    it again. A value in cell A4 cannot be 0, because it shows the amount of
    items. And it is +7, because the first item is not on the first row, but
    on the 7th. I use this variable (AmountOfItems) just to specify the cell
    of a 1st empty row after the last item.

    So do you think there's a way to put this variable into RANGE("")? or should
    I use some other method to insert a dynamic comment?

    With respect,
    Dmitri



    "Antti" <antti_komonen@hotmail.com> wrote:
    >
    >There are couple of problems with your code sample:
    >
    >Variable AmountOfItems gets whatever value is in cell A4 of first worksheet
    >in active workbook plus 7. For example if cell A4 is empty AmountOfItems
    >equals 7. I don't know what you are trying to do here but this does not

    seem
    >right.
    >
    >Range("AmountOfItems").AddComment means you are trying to add comment to
    >a named range. If named range "AmountOfItems" does not exist in active workbook
    >this should result in error.
    >
    >In general if you are using Excel from within a VB application I would advice
    >you to first get the code working in Excel and only after that export it
    >to your VB application. Debugging is much easier that way.
    >
    >Antti
    >
    >"Dmitri" <dimiok_1@yahoo.com> wrote:
    >>
    >>Guys, I have the same problem as Makkineni, except that it cannot be solved
    >>using the solutions u posted here. OK, I want a comment to be inserted

    at
    >>the end of a large list of items (600+). And because insert comment option
    >>only works with range as far as I know, I have to put that variable there,
    >>so that the comment is put after the last item. So here's the code I use:
    >>
    >>Private Sub TextBox4_Change()
    >>AmountOfItems = Worksheets(1).Cells(4, 1) + 7 <--variable of the cell

    where
    >>the comment should be inserted<--
    >>Worksheets(1).Select
    >> Range("AmountOfItems").AddComment
    >> Range("AmountOfItems").Comment.Visible = False
    >> Range("AmountOfItems").Comment.Text Text:="unit: piece ""
    >> Range("AmountOfItems").Select
    >>End Sub
    >>
    >>Any help will be appreciated
    >>
    >>With respect,
    >>Dmitri

    >



  11. #11
    Paul Clement Guest

    Re: Use of a variable as a parameter fro Range property

    On 8 Oct 2001 04:18:55 -0700, "Dmitri" <dimiok_1@yahoo.com> wrote:


    Hi, thanks for you response Antti.
    Ok, I think you a little misunderstood my code. So, I'm gonna try and explain
    it again. A value in cell A4 cannot be 0, because it shows the amount of
    items. And it is +7, because the first item is not on the first row, but
    on the 7th. I use this variable (AmountOfItems) just to specify the cell
    of a 1st empty row after the last item.

    So do you think there's a way to put this variable into RANGE("")? or should
    I use some other method to insert a dynamic comment?


    Yes, you can use a variable name (to hold the range name) when specifying the range. Just make
    certain not to enclose the variable name within double quote marks.

    Dim AmountOfItems as String

    'cell ref
    AmountOfItems = "A1"

    ' - or -

    'range name
    AmountOfItems = "MyRange"

    Range(AmountOfItems).AddComment


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  12. #12
    makkineni Guest

    Re: Use of a variable as a parameter fro Range property


    Hi Guys,
    I got it working.
    It worked with 'cells'.
    Thanks for all the help.
    -Makkineni
    Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >On 8 Oct 2001 04:18:55 -0700, "Dmitri" <dimiok_1@yahoo.com> wrote:
    >
    >
    > Hi, thanks for you response Antti.
    > Ok, I think you a little misunderstood my code. So, I'm gonna try and

    explain
    > it again. A value in cell A4 cannot be 0, because it shows the amount

    of
    > items. And it is +7, because the first item is not on the first row, but
    > on the 7th. I use this variable (AmountOfItems) just to specify the cell
    > of a 1st empty row after the last item.
    >
    > So do you think there's a way to put this variable into RANGE("")? or

    should
    > I use some other method to insert a dynamic comment?
    >
    >
    >Yes, you can use a variable name (to hold the range name) when specifying

    the range.
    >Just make
    >certain not to enclose the variable name within double quote marks.
    >
    >Dim AmountOfItems as String
    >
    >'cell ref
    >AmountOfItems = "A1"
    >
    >' - or -
    >
    >'range name
    >AmountOfItems = "MyRange"
    >
    >Range(AmountOfItems).AddComment
    >
    >
    >Paul ~~~ pclement@ameritech.net
    >Microsoft MVP (Visual Basic)



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center