Results 1 to 9 of 9

Thread: [RESOLVED] Variables used as excel cell coordinates

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    4

    Resolved [RESOLVED] Variables used as excel cell coordinates

    Hello,

    I'm trying to create a little program behind a excel(2003) button. I understood the language to use is Visual Basic and hopefully I'm in the right section of the forum.

    I have this piece of code so far :

    Code:
    For x = lines_start To lines_limit
    For y = coulumns_start To columns_limit
    
    k = 0
    For i = 1 To cond_nr
    
          
    'verifying first possible value
    
    vertically_available = yes                  'vertically
    For j = lines_start To lines_limit
    If Sheet1.Cells(y, j) = possibleValues(i) Then
    vertically_available = no
    Exit For
    End If
    Next
    
    
    If vertically_available = yes And horizontally_available = yes Then
    k = k + 1
    finalValues(k) = possibleValues(i)
    End If
    End If
    
    
    Next
    
    'randomvalue = CInt(Int((upperbound - lowerbound + 1) * Rnd() + lowerbound))
    luck = CInt(Int((k - 1 + 1) * Rnd() + 1))
    Sheet1.Cells(x, y) = finalValues(luck)
    
    Next
    Next
    My problems seems to be in the lines with red where i reefer to a cell using variables for the cell's coordinates. First to compare the value from that cell with something, as an if condition, second time, to assign a certain value from an array to a cell. The error I get is "Application-defined or object-defined error" .
    (obviously all the variables and arrays used are correctly declared but I didn;t post that part of the program too)

    Does anyone have any idea how to overcome this ?

  2. #2
    New Member
    Join Date
    Apr 2012
    Posts
    4

    Re: Variables used as excel cell coordinates

    Have you created the excel workbook object........
    can you post that part of the code also.......

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    4

    Re: Variables used as excel cell coordinates

    Quote Originally Posted by sneha2409 View Post
    Have you created the excel workbook object........
    can you post that part of the code also.......
    Hello,
    I haven;t created the excel workbook object. It seems that excel sheet already exists as an object: In my Microsoft Visual Basic Menu (launched via Excel) I have in the right under the name of my excel file a list with all the objects of this file (each excel sheet is an object).

    Another observation: in the lines in red (written in the above post) where the debugger finds the error, if I use instead of variables numbers everything works fine :
    Sheet1.Cells(x, y) = finalValues(luck) - I get errors
    Sheet1.Cells(1, 1) = finalValues(luck) - works (but it is not what I want.

    However , please tell me how to declare this object (I guess the object should be Sheet1 ? ) and I will try.

    Also I'll insert below the the entire code (including many irrelevant parts, in case I missed anything) :

    Code:
    Private Sub CommandButton1_Click()
    Dim finalValues(1 To 3), possibleValues(1 To 3), remainedValues(1 To 3) As String
    Dim l, j, k, i, luck, x, y, lines_limit, columns_limit, lines_start, columns_start, vecin_stanga, vecin_dreapta, vecin_sus, vecin_jos As Integer
    Dim cond_nr As Integer
    Dim stanga_value, dreapta_value, sus_value, jos_value, vertically_available, horizontally_available As String
          
    lines_start = 2
    columns_start = 2
    lines_limit = 4
    columns_limit = 4
          
          
    cond_nr = 3
    possibleValues(1) = "Deftones"
    possibleValues(2) = "Tool"
    possibleValues(3) = "Disturbed"
    
    For x = lines_start To lines_limit
    For y = coulumns_start To columns_limit
    
    k = 0
    For i = 1 To cond_nr
    
          
    'verifying first possible value
    
    vertically_available = yes                  'vertically
    For j = lines_start To lines_limit
    If Sheet1.Cells(y, j) = possibleValues(i) Then
    vertically_available = no
    Exit For
    End If
    Next
    
    If i = 1 Then
    horizontally_available = yes                'horizontally
    For l = columns_start To columns_limit
    If Sheet1.Cells(x, l) = possibleValues(i) Then
    horizontally_available = no
    Exit For
    End If
    Next
    
    
    If vertically_available = yes And horizontally_available = yes Then
    k = k + 1
    finalValues(k) = possibleValues(i)
    End If
    End If
    
    
    Next
    
    'randomvalue = CInt(Int((upperbound - lowerbound + 1) * Rnd() + lowerbound))
    luck = CInt(Int((k - 1 + 1) * Rnd() + 1))
    Sheet1.Cells(1, 3) = luck
    Sheet1.Cells(1, 1) = finalValues(luck)
    
    Next
    Next
    
    MsgBox "done "
    
    End Sub

  4. #4
    PowerPoster Spoo's Avatar
    Join Date
    Nov 2008
    Location
    Right Coast
    Posts
    2,656

    Re: Variables used as excel cell coordinates

    BF

    Welcome to the Forums ..

    Question ... are you doing this
    • using a VB6 app to interact with an Excel workbook
    • all within Excel


    If it is the latter, then technically speaking, you are using VBA.
    While VBA is similar to VB6, theoretically, you should be in the Office Development Forum.

    Spoo

  5. #5

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    4

    Re: Variables used as excel cell coordinates

    Quote Originally Posted by Spoo View Post
    BF

    Welcome to the Forums ..

    Question ... are you doing this
    • using a VB6 app to interact with an Excel workbook
    • all within Excel


    If it is the latter, then technically speaking, you are using VBA.
    While VBA is similar to VB6, theoretically, you should be in the Office Development Forum.

    Spoo
    Hello, yes I think is VBA. I'll adress this question there and ask the admins to close this thread.

    Thank you!

  6. #6
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Re: Variables used as excel cell coordinates

    The error may not be related to this but: declare all your variables specifying type for each of them, the way your're doing it now only the last variable on each line has the correct type. Example (I added the code in red):
    Code:
    Dim finalValues(1 To 3) As String, possibleValues(1 To 3) As String, remainedValues(1 To 3) As String
    Do the same with all your variables. VB assumes that variables without type are Variants.

    Side Note: Please! indent your code, not just here, in your project. Do it using TAB key, it's the best way, your code will be much easier to read for you and also for us
    Last edited by jcis; Apr 24th, 2012 at 11:21 AM.

  7. #7
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

    Re: Variables used as excel cell coordinates

    You have a typo in this line:
    For y = coulumns_start To columns_limit
    Because of that your "y" has a value of zero!
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  8. #8
    PowerPoster Spoo's Avatar
    Join Date
    Nov 2008
    Location
    Right Coast
    Posts
    2,656

    Re: Variables used as excel cell coordinates

    BF

    OK, glad we got the VBA bit confirmed.

    Using Jcis's suggestion, here is your OP, with indents.
    I took the liberty to edit the Next statements by adding the
    identifying "counter".
    Code:
    For x = lines_start To lines_limit
        For y = coulumns_start To columns_limit
            k = 0
            For i = 1 To cond_nr     
                'verifying first possible value
                vertically_available = yes                  'vertically
                For j = lines_start To lines_limit
                    If Sheet1.Cells(y, j) = possibleValues(i) Then
                        vertically_available = no
                        Exit For
                    End If
                Next j
                If vertically_available = yes And horizontally_available = yes Then
                    k = k + 1
                    finalValues(k) = possibleValues(i)
                End If
                End If
            Next i
            'randomvalue = CInt(Int((upperbound - lowerbound + 1) * Rnd() + lowerbound))
            luck = CInt(Int((k - 1 + 1) * Rnd() + 1))
            Sheet1.Cells(x, y) = finalValues(luck)
        Next y
    Next x
    For starters, you seem to have an unmatched End If.
    Such a situation will cause a crash, but the "reported error" is generally
    unreliable .. usually something else is the issue.

    See what happens if you fix that.

    EDIT:

    I just saw Opus's spot .. nice one ..
    I have also highlighted that in red.

    Here, too, is the balance of your code from your post #3.
    I also took the liberty to add some extra Dim lines (to eliminate
    the horizontal scroll bar for ease of reading), but did not address
    the issue that Jcis brought up .. you still need to fix that.

    Code:
    Private Sub CommandButton1_Click()
        Dim finalValues(1 To 3), possibleValues(1 To 3), remainedValues(1 To 3) As String
        Dim l, j, k, i, luck, x, y, lines_limit, columns_limit As Integer 
        Dim lines_start, columns_start  As Integer
        Dim vecin_stanga, vecin_dreapta, vecin_sus, vecin_jos As Integer
        Dim cond_nr As Integer
        Dim stanga_value, dreapta_value, sus_value, jos_value As String
        Dim vertically_available, horizontally_available As String
        ' set vars
        lines_start = 2
        columns_start = 2
        lines_limit = 4
        columns_limit = 4
        cond_nr = 3
        possibleValues(1) = "Deftones"
        possibleValues(2) = "Tool"
        possibleValues(3) = "Disturbed"
        < ... see code snippet above ... >
        MsgBox "done "
    End Sub
    Finally, a suggestion to minimize typo's of the kind that Opus caught ...
    • Always use a few CAPITAL letters when you Dim your variable names.
    • Thereafter, when you type the variable name in lower case, the VBA Editor
      will automatically capitalize it for you.
    • This is a visual clue .. if you type some var name and no capitals appear
      >> oops !


    So, something like this (incomplete list, but does use Jcis's suggestion)
    Code:
    Dim Lines_Limit As Integer
    Dim Columns_Limit As Integer
    Dim Lines_Start As Integer
    Dim Columns_Start As Integer
    Dim Cond_NR As Integer
    Spoo
    Last edited by Spoo; Apr 24th, 2012 at 12:16 PM.

  9. #9

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    4

    Re: Variables used as excel cell coordinates

    Hello to all,

    Yes, my problem was solved after correcting my typo (thanks a lot Opus & Spoo) . Also all your advices are noted (regarding variable declaration .. etc)

    Very warm community you have here .

    Cheers !

Posting Permissions

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



Click Here to Expand Forum to Full Width