Results 1 to 11 of 11
  1. #1
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post

    Bookmarks in VB code

    As code in my project gets longer by adding more standard modules and sheets, I am attempting to keep track of certain codelines with bookmarks. For example, If I add a new column of data, I have to make changes in 10 various places. Placing bookmarks to the left of the code line works great but when I save the file then reopen it, the bookmarks are gone.

    Does anyone know how to save the bookmarks so they are available between sessions?

    TIA
    Lexi

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Although you did not mention which Office version you are using, I am running Office 2010 and have had the same issue. I haven't been able to find much on this topic outside of Visual Studio, therefore, I use bookmarks if I only want to jump around in the current opening. For a more permanent solution, there are several work-arounds that I use:

    Create a unique comment on the specific lines then use the Find and Find Next to search for the comment. I make the needed changes to the line then search for the next one and repeat the process. For Example:

    Code:
    For I= 1 to 20  'CHANGE IF NEW COLUMNS ADDED
    Sometimes when I want to find an isolated line of code, I add a line that intentionally causes a compile error when I attempt to compile, not run, the code (Debug> Compile project name) OR (Alt-d-l). This will bring you right back to the code line. An example would be to update code in one module necessitates an update of code in a different module.

    Code:
    Public Sub test()
    CorrectWithNewColumn
    num = Int(Cells(1, 1))
    End Sub
    I attempt to use variables for values that may change as changes are made to the workbook. I would then declare and set those variables up front so they apply to the rest of the code. In you example, if you loop based on the number of columns then you could maintain a variable for the number of columns. Changing the variable will also make the necessary changes in other parts of the code.
    Code:
    Public Sub test()
    Col = 10  'CHANGE VALUE 
    For I = 1 To Col
        --code goes here--
    Next I
    End Sub
    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Lexi (2014-02-01)

  4. #3
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    Maudibe,

    I see the logic of setting as many parameter to variables as you suggested. There is a lot of looping based on the number of columns and changing a loop variable will reduce most of the subsequent changes that are needed. Also, each column needs a similar, but not exact, block of code in various modules. Adding a unique comment at each of those points will serve well as a bookmark. This will help a great deal as more columns are being added each week. This is a great time saver! Thanks Maud

    Lexi

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 645 Times in 589 Posts
    You're welcome. Thanks for the Thanks

  6. #5
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    Maudibe,

    Just adding in the comments to act as bookmarks and it occured to me that I could use something like bookmark: at the spot I want to return to using Find.

    Lexi

  7. The Following User Says Thank You to Lexi For This Useful Post:

    Maudibe (2014-02-04)

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Sweet! Labels will work. Just remember that you can't use the same label twice in the same procedure

    Maud

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    Lexi (2014-02-06)

  10. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Lexi View Post
    If I add a new column of data, I have to make changes in 10 various places.
    Lexi
    Lexi,

    Have you looked into making the code determine how many & which columns have to be operated on so that the code adjusts automatically when an column is added. This can often be done with the use of dynamic range names, Module or Global Variables that only need to be changed in one place, and logic if necessary based on your code. In the short run this is more time consuming but in the long run will save much time and is less prone to the introduction of errors in the editing process. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lexi (2014-02-06)

  12. #8
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    Retired Geek

    I have changed all the loop numbers for the columns to variables and defined the variables as Maudibe suggested in post #2 as well as created the variables for values that need to change as I add more columns. Are you suggesting something different?

    Lexi

    BTW, how do I put a picture for my name?

  13. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Lexi,

    Quote Originally Posted by Lexi View Post
    I have changed all the loop numbers for the columns to variables and defined the variables as Maudibe suggested in post #2 as well as created the variables for values that need to change as I add more columns. Are you suggesting something different?
    Yes, you can write code to determine the values for the loop variables. Of course, this does depend on the structure of your worksheet. You can use Dynamic Range Names which will automatically adjust when columns and/or rows are added, etc.

    how do I put a picture for my name?
    Click Here

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lexi (2014-02-06)

  15. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Lexi,

    As RG suggests, automatically determine the loop value, the following line is one way to automatically do so as new columns are added:

    LastCol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column

    where the 1 represents the index row that it is checking for data. In the example above, the code line goes to the last possible column of a worksheet (XFD)then works to the left in row 1 until it comes across a value which will be the last column
    Code:
    Public Sub test()
    LastCol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    For I = 1 To LastCol    
         --code goes here--
    Next I
    End Sub

  16. The Following User Says Thank You to Maudibe For This Useful Post:

    Lexi (2014-02-06)

  17. #11
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    I have seen this frequntly used in code to find the last row but was not aware that it could be used to find the Last column. Thanks RG and Maud

Posting Permissions

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