Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change VBA code during runtime

    I have made a template with a macro attached. The macro is running a non-linear curve fitting procedure. The X values for the fit are hardcoded in the macro. The Y-values have to be entered on the template by the user. Now, to make it more flexible I want to add another macro (button on the template) that makes it possible to change the X-values.
    Up to me, this is not something that can be done, without the use of a second file (e.g. txt file) where these values are written to and read from. But that, of course, makes it more complicated because then I have to distribute two files (the Excel template and the txt file), and on top of that, I have to be sure that the text file is stored on a known location.
    Since I learned from Andrew, that the VBE can be programmed, I was wondering if the code attached to a workbook can be changed by running another macro. In that case, I would read the lines of code with my X-values, and replace them with the new values, and save the code.
    Just to make sure that we understand each other: an add-in is not a solution. Our marketing people want to have it as simple as possible for our customers and a template is still acceptable. One button on that template runs the least-squares fit, the other button runs a macro that changes the X-values (if feasible). Any ideas?

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

    Re: Change VBA code during runtime

    Why not put the X values on a separate worksheet in the template. You can hide this sheet if you want, and the VBA code can simply pick up the values from there. When you want to change the X values, you simply unhide the sheet, change them and re-hide the sheet. You can use a button to do the hide and unhide if you want.
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change VBA code during runtime

    Excellent idea, Legare. I thought that a template was actually only one worksheet. Is this hidden worksheet then part of the workbook created by that template? In other words, can the user unhide the sheet? Of course I can password protect this sheet so that he cannot change its contents. I surely try this out. Thanks.

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change VBA code during runtime

    How about .visible = xlVeryHidden?

    Brooke

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Change VBA code during runtime

    If you assign the X value a worksheet variable (called say X_Value) and then in a cell on a hidden sheet enter = X_Value. The value can then be changed by something like the following :<pre> Sub Change_XValue()
    X = InputBox("Enter New Value for X: ")
    ActiveWorkbook.Names.Add Name:="X_Value", RefersTo:=X
    End Sub</pre>

    and there is no need unhide the sheet.

    Andrew

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change VBA code during runtime

    Thanks for the suggestion, Andrew. I still have one problem: if I use my template (File >> New) then a workbook is created with the code attached and changing the X-values actually changes these values in that workbook, not in the template. So, next time the user opens a file from that template, he needs to change the X-values again. There is no way, I think, to change the X-values programmatically in the template itself?

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

    Re: Change VBA code during runtime

    If you hide the sheet in the template and then create a workbook from the template, then the user would be able to unhide the sheet. The sheet will show up on the Format/Sheet/Unhide list. If you don't want the user to be able to unhide the sheet, then you can set the Visible property for that sheet to xlVeryHidden using VBA code before you save the template. When you do that, the sheet does not show up on the Format/Sheet/Unhide list, and the user can only unhide it if he knows the sheet's name and writes VBA code to set its visible property to True.

    You are correct that if you change the X values on the sheet in a workbook created from the template, that will only change those values in that workbook, not in the template. At this point, I would need to know more about what you want to happen to make any further recommendations. If the X values are changed, you indicated that you want them to change in the template and the workbook that was created from it. How about any other workbooks that the user may have created from the template? If this is being used by other users, how about their templates and workbooks? It is certainly possible to change the template, but you would have to have VAB code to open the template, change it, and then save and close it. However, doing that would not pass the changes on to other workbooks created from the template. If you want to have the changes in all workbooks created from the template, you probably want to put the X values in the Registry or into a separate file.
    Legare Coleman

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change VBA code during runtime

    Legare,

    The reason for having the opportunity to change the template is the following. My company is making a lab-test that uses a standard solution with known concentration. The user has to dilute the standard in a sequential series of 1/2 to end up with a standard series of 5. For these 5 standards he knows the concentration (these are the X-values). He then has to measure an optical density (the Y-values) and fit the calibration curve (the concentration vs. OD-values). The OD-value of Unknown samples are then measured and the concentration is calculated from the curve. Such a lab-test can only be used once, because the chemicals are used. Our clients buy more lab-tests, containing these standard, but the standard concentration can change at a certain point in time (e.g. out of stock). What I actually want is that they use a template for the calculations and the report of the results. At one point in time they should be able to change the standard concentrations on the template, not on the workbooks that they have generated with previous lab-tests.
    You are right that maybe I need to browse, open the template, change the values and close it again. The xlVeryHidden suggestion is what I'll use to hide the actual X-values from unwanted user access.
    Hope this clarifies my original question. I can imagine that it sounded a little bit weird. Thanks for your helpful comments.

  9. #9
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change VBA code during runtime

    Try:

    <A target="_blank" HREF=http://www.j-walk.com/ss/excel/tips/tip61.htm>http://www.j-walk.com/ss/excel/tips/tip61.htm</A>

    <A target="_blank" HREF=http://www.cpearson.com/excel/vbe.htm>http://www.cpearson.com/excel/vbe.htm</A>

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

    Re: Change VBA code during runtime

    Based on that description, I can think of several possibilities:

    1- It sounds like the last four concentrations can be calculated given the first. You could specify the first concentration in the test kit and require that it be entered in the sheet with a check digit to make sure it was entered correctly. Then calculate four of the values from the first.

    2- If there is a finite number of concentrations, then include all of the possibilities on hidden sheets in the template and have the user enter something that could be used to find the correct table.

    3- Put the X values in a separate workbook that could be updated/reloaded from information in the test kit. Possibly a bar code reader could be used to read the values from a barcode in the kit. A reader would probably be less that $100 today.

    4- Put code in the template that would update the template when new X values are entered.

    5- Let the template get the X values from a site on the Internet.
    Legare Coleman

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change VBA code during runtime

    Thanks for all these suggestions. You know, each of them is worth trying out, but I am not the only one deciding on this issue. Marketing takes the final decision, I am only providing the technical possibilities: what can be done and what are the consequences of the choices. I think I have enough stuff to offer from which they can make their final choice (don't let marketing people have to many options, they will never be able to make a final and fixed decision).
    Thanks again.

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

    Re: Change VBA code during runtime

    Kind of like lawyers! Give 5 Marketing people a choice between 2 alternatives, and you will get 32 decisions (=2**5).
    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
  •