1

I am trying to see if it is possible to have a macro edit itself in a permanent way. From what I have found on the internet it is potable to do something with the macro but I am unsure if I can do something more permanent.

The Idea is this.

I have a list my MACRO follows to determine who will be assigned a ticket number based on customer number. However if the customer is new then the MACRO will automatically select a person from the list to permanently add the customer to. I can do this with a separate spreadsheet being used for the list but I would rather hard-code the list into the MACRO and then add the new customer numbers to the hard-coded list so it is there for the next time the MACRO is ran.

This will prevent the need for a 2nd spreadsheet and removing the need to make sure the 2nd spreadsheet is in the correct folder and named properly. Cutting out the 2nd sheet all together feels like a better option.

I imaging I can do this with an Array so my question becomes:

Is it possible to use the MACRO to edit a hard-coded Array within the MACRO permanently so the new values are in the Array on the next time the MACRO is ran.

braX
  • 11,506
  • 5
  • 20
  • 33
Mike - SMT
  • 14,784
  • 4
  • 35
  • 79
  • Why don't you just add a hidden sheet *in the same workbook*? – Comintern Oct 08 '18 at 17:05
  • 1
    Although possible, is it truly *desirable*? What about the maintenance of your code? You are simply storing this data as plain text inside your code, which seems like a terrible idea. Why not store it properly, in a database, or simply in another hidden/very hidden sheet? – fabio.avigo Oct 08 '18 at 17:07
  • In case you are worried about reloading your array values everytime you want to run your macro, a way to solve it is to initialize your array in a Workbook open event and keep it "alive" as a global variable. – fabio.avigo Oct 08 '18 at 17:09
  • @Comintern I cannot add a sheet because it is a report we receive ever day. Its quicker to just paste in the macro and press go. – Mike - SMT Oct 08 '18 at 17:16
  • @fabio.avigo The thing is we get a new report daily and the macro then needs to be copy pasted into that workbook and ran. I guess it is probably better to just keep the 2nd workbook. – Mike - SMT Oct 08 '18 at 17:18
  • So if I understand correctly, you want to automate pasting the macro in? – Comintern Oct 08 '18 at 17:19
  • @Comintern no. The idea was to have a hardcoded string array that I could add new values to that would then become permanent on that macro so the next time it is ran it will already have those new values from the last time. The more I think about it the more I realize it is probably just best to keep the 2nd speadsheet that hold my list. – Mike - SMT Oct 08 '18 at 17:20
  • @Mike-SMT, there is another vulnerability at hand here: how do you make sure the proper code version is copy-pasted on your workbook? A good way to solve this is to keep a standard .xlsm file that stores the code and all needed references, that you point to the workbook that needs the macro being run on. Everyday, just open this *master* file, change the address of the newly received workbook and run your macros. – fabio.avigo Oct 08 '18 at 17:21
  • @Comintern here is one reason I am trying to do this. Each time I need to take a new customer number and assign it to a person I need to keep track of who was the last person to get an assignment. It would be useful to be able to update a string variable with the next name of who needs to be assigned. – Mike - SMT Oct 08 '18 at 17:22
  • @Comintern I am unfamiliar with "common .bas module" I am a Python programmer and relatively new to VBA so some things I am not yet aware of. – Mike - SMT Oct 08 '18 at 17:24
  • @fabio.avigo That makes sense. I could just keep the macro on the workbook with the list and then load the report for processing from there. I think I will do that instead. I still wonder if/how one could have a MACRO edit itself and then keep the changes after the code finishes running. – Mike - SMT Oct 08 '18 at 17:27
  • 3
    *I can do this with a separate spreadsheet being used for the list but I would rather hard-code the list into the MACRO and then add the new customer numbers to the hard-coded list* - since when is **data** better off hard-coded? – Mathieu Guindon Oct 08 '18 at 17:29
  • @MathieuGuindon the list is relatively small and I was trying reduce the amount of work the user needed to do for each report. I have decided to do something else instead because in this case it is not very practical. That said it still an idea I want to explore. There may be some use for a self editing macro down the road. – Mike - SMT Oct 08 '18 at 17:32
  • I think what you want is to create a simple Excel Add-In that will be present for ALL of the workbooks you open up, including the new ones. As others have suggested, you can store the list of users within the add-in tabs and make a "hidden" and "permanent" list of users that can be manipulated. Creating an add-in isn't difficult. You can even create a new "button" on one of the Excel ribbons to kick off your process. – PeterT Oct 08 '18 at 18:00
  • 1
    @fabio.avigo: can you point out a source, where "self-rewriting" vba code is explained? Just for sake of completeness – prextor Oct 09 '18 at 12:08
  • 1
    @prextor I don't have any formal source, but a google search points out several ways to do it. One could, for example, export the modified .bas file at the end of an execution and reload it on the next. Another option is to change it during runtime with the Module under Workbook.VBProject.VBComponents.Item.CodeModule. However, it is a troublesome task ensuring stability for self-modifying code, be it on any language. – fabio.avigo Oct 10 '18 at 11:19

0 Answers0