spreadsheet macros and tables

[sts8500man]

OK. It appears no one is going to respond to my plea for help with creating dynamic charts from dynamic name ranges using the offset function. After repeated tries over too long a period of time to even talk about, I am finally giving up on creating dynamic charts in windows 10 and office 365. That used to work just fine when I was using vista and before office 365, now, I can't create a dynamic chart to save my mind and cannot figure how to do so from the myriad internet postings of it. Admittedly, I am not a programmer. I am a single user that is self educated on the various functions associated with Excel. That Offset function is difficult to understand but I finally understood it. However, I still cannot get my charts to update dynamically. Putting the range names into the series formulas seems to be the problem. I have tried everything imaginable to do that without success. I am frustrated.

So, I am about to give up but my alternatives are less than desirable. I have resisted using tables because I don't know a blooming thing about tables. For instance, if I convert my excel spreadsheet to a table will my VBA macros in the spreadsheet still work? I don't know and cannot find the answer to that. The other thing I am considering is ditching Excel in favor of Open Office Calc but doing that would cause me to have to start from scratch on a new learning curve for programming macros in Calc.

Would anyone care to comment on this? If not, I understand. You are probably tired of hearing about dynamic charting, but if that is the case, put the blame where it belongs. Microsoft apparently did something to the updates past those for Vista to screw up something that used to work just fine. Why is beyond my imagination.

 

[Col Delane]

Howdy

I know how frustrating it is when lots of users view your post but no-one provides an answer. Unfortunately, this forum has become so successful/popular, that there is now a far greater demand for answers than knowledgeable users to provide useful answers. I am only just returning to the forum after a month or so and have replied to about six posts today - starting at the oldest in the Zero Posts list - but even I have been very selective. some posts are just too hard to respond to in this context.

Now, about your issue/s:

1. I don't know whether or not Office 365 changes the way Excel handles charts and/or dynamic ranges.
2. I do know that Tables are very useful (but have some downsides) and take a bit to get used to. There are numerous resources (video and narrative tutorials) available on the web so spend an hour or so to .
3. Tables are a completely different VBA animal to ordinary cell ranges, and so have different VBA objects, properties and methods. As such and unfortunately, your old VBA will NOT work on a Table without modification.
4. I have used dynamic ranges using OFFSET in charts on a number of occasions, but they do take a bit to get your head around and there are several tricks and traps - and I need to refresh my memory each I need to use them in a chart!
5. John Peltier is one of the world's gurus on Excel charting - check out his site at https://peltiertech.com or search the web for answers.
6. Ideas:
   1. As Defined Names are just named formulas, try including the workbook name (just like in a linked formula between files) when specifying the Name as a chart parameter. Unfortunately, I think you have to type in the full string rather than pointing or selecting from a dialogue box.
   2. Ensure all your dynamic ranges used in the same chart of synchronised (i.e return the same number of axis and data points). This may necessitate replicating in the data series Named Ranges part of the formula used in the Offset function to return the Axis points, or leveraging off the axis range to return the data series range (e.g. if the axis is a date time line, use this as the base upon which you "calculate" your data series ranges by using a parallel (e.g. row) offset from the axis/date range.

I hope this helps you move forward.

 

[sts8500man]

ATT: Col Delane,

You have no idea how much I appreciate you taking time to respond to my post. I am very sure that all Excel experts that are willing to help people such as myself on mrexcel are very busy keeping their own businesses or their employers a-float. I have been there. I understand and therefore have a great appreciation for responses.

I have tried everything you suggest and worked extensively at peltiertech.com as well as at the website of the lady he refers people to, I cannot think of her name right now. I have tried numerous times to follow instructions extensively on a step-by-step basis and still the charts I generate in Office 365 and Windows 10 will not dynamically update. I think maybe there is something wrong with my spreadsheets.

The interesting thing is that I have two old spreadsheets and attendant charts that date back to using Windows Vista, and whatever routinely updated Excel was consistent with Vista, that were kept when moving to a new computer with windows 10 and office 365. Those two work just fine!! They update dynamically just like they did under Vista. But no new charts from new spreadsheets that I generate under windows 10 and Office 365 will dynamically update. I don't have a problem with the spreadsheets updating with new data but the charts remain static. Fact is -- I am just plain tired messing with it and will be content to generate a new static chart in each case every time I want or need to look at the data graphically. I do not want to start new learning curves for either Excel Tables and attendant VBA and macros, or Open Office Calc and its attendant macros.

Unless I have something intrinsically wrong with my spreadsheets, and I have no idea what that could be, it just looks to me like Microsoft purposely made something that was easy and simple into something difficult with Windows 10 and Office 365 and I cannot understand why.

Anyway, thank you again for responding and I wish you the best of everything.

sts8500man

Howdy

I know how frustrating it is when lots of users view your post but no-one provides an answer. Unfortunately, this forum has become so successful/popular, that there is now a far greater demand for answers than knowledgeable users to provide useful answers. I am only just returning to the forum after a month or so and have replied to about six posts today - starting at the oldest in the Zero Posts list - but even I have been very selective. some posts are just too hard to respond to in this context.

Now, about your issue/s:

1. I don't know whether or not Office 365 changes the way Excel handles charts and/or dynamic ranges.
2. I do know that Tables are very useful (but have some downsides) and take a bit to get used to. There are numerous resources (video and narrative tutorials) available on the web so spend an hour or so to .
3. Tables are a completely different VBA animal to ordinary cell ranges, and so have different VBA objects, properties and methods. As such and unfortunately, your old VBA will NOT work on a Table without modification.
4. I have used dynamic ranges using OFFSET in charts on a number of occasions, but they do take a bit to get your head around and there are several tricks and traps - and I need to refresh my memory each I need to use them in a chart!
5. John Peltier is one of the world's gurus on Excel charting - check out his site at https://peltiertech.com or search the web for answers.
6. Ideas:
   1. As Defined Names are just named formulas, try including the workbook name (just like in a linked formula between files) when specifying the Name as a chart parameter. Unfortunately, I think you have to type in the full string rather than pointing or selecting from a dialogue box.
   2. Ensure all your dynamic ranges used in the same chart of synchronised (i.e return the same number of axis and data points). This may necessitate replicating in the data series Named Ranges part of the formula used in the Offset function to return the Axis points, or leveraging off the axis range to return the data series range (e.g. if the axis is a date time line, use this as the base upon which you "calculate" your data series ranges by using a parallel (e.g. row) offset from the axis/date range.

I hope this helps you move forward.

 

[Col Delane]

I do not want to start new learning curves for either Excel Tables and attendant VBA and macros, or Open Office Calc and its attendant macros. sts8500man

Don't give up on Tables - the learning is not that much; they're just different (a bit like learning Pivot Tables the first time!) but can be very useful (especially when imported data, or Data Validation source ranges, etc. vary in length) even if you don't use any VBA

This guide is pretty comprehensive https://www.maxwell.syr.edu/uploadedFiles/ict/Training/Handouts/WorkingWithExcelTablesHandout.pdf

... it just looks to me like Microsoft purposely made something that was easy and simple into something difficult ...

Now who woulda thunk that? Surprise!!

 

[sts8500man]

Col,

Than you again for responding. Because of your encouragement to do so, I might try using tables by converting existing spreadsheets to tables. However, before I begin that, there are a couple of basic issues that I need to know. I did not find a reference to these issues in the pdf document that you provided a link to. Those issues are:
1. When I import the data to the table instead of an excel spreadsheet, I need the data to go into the top row of the table whilst all other rows get push down to accommodate the new data in the top row. Can that be done when importing data to an existing table?
2. In that pdf document I also did not see a reference to the possibility of converting my existing VBA macros in my existing spreadsheet to their corresponding macros in a table document. Can VBA spreadsheet macros be converted like that or do I have to start from scratch with a table macro builder to get the table to do things automatically?

If I cannot get macros to work in a table document in a manner similar to how they work in a spreadsheet, then moving to tables is not an option. The process of importing data, although not done on a daily basis is very extensive. Doing it by hand simply is not an option.

sts8500man

Don't give up on Tables - the learning is not that much; they're just different (a bit like learning Pivot Tables the first time!) but can be very useful (especially when imported data, or Data Validation source ranges, etc. vary in length) even if you don't use any VBA

This guide is pretty comprehensive https://www.maxwell.syr.edu/uploadedFiles/ict/Training/Handouts/WorkingWithExcelTablesHandout.pdf




Now who woulda thunk that? Surprise!!

 

[Col Delane]

... I might try using tables by converting existing spreadsheets to tables.

Take baby steps!! You can only eat an elephant one bite at a time.
Start slowly with small applications that don't involve using VBA (e.g. as a VLOOKUP table or as a Source Range for a Data Validation option list that may need to be added to over time) until you get the hang of how they work, how the automatic replication of formulas work, and how to write formulas that reference data within the table (where the formula is located both within and outside the table)

1. When I import the data to the table instead of an excel spreadsheet, I need the data to go into the top row of the table whilst all other rows get push down to accommodate the new data in the top row. Can that be done when importing data to an existing table?

If you know the size of the import data you can insert the rquired number of blank rows and then paste the data into those, but why do you need data to go into the top row of the table? Is there not some key field that you can use to find the required rows within the table no matter where they are located? Or is it possible to assign a unique index number or date to each batch of imported data that will allow you to sort the table?

2. In that pdf document I also did not see a reference to the possibility of converting my existing VBA macros in my existing spreadsheet to their corresponding macros in a table document. Can VBA spreadsheet macros be converted like that or do I have to start from scratch with a table macro builder to get the table to do things automatically?

I'm not aware [but have never looked!) of any "wizard" that will clean up / convert VBA code that acts on a range to act on an Excel Table. As mentioned, Tables are a different animal (List Objects rather than Range objects), so whilst the end goal (what you want to do with the data) is still the same it's how the code goes about it is different (i.e. the references to the target area, and the identification of the Properties of those objects, and the Methods to manipulation them are different.)

If I cannot get macros to work in a table document in a manner similar to how they work in a spreadsheet, then moving to tables is not an option. The process of importing data, although not done on a daily basis is very extensive. Doing it by hand simply is not an option.

I understand your position, especially if there is any size, complexity or criticality in your existing application - I had a similar issue some time ago. Having said that, as you learn about tables, you may get to the point where it's not such a big deal to convert. Also be aware that much of what we did in the past with data ranges and the manipulation thereof is being rapidly overtaken by the new database and business intelligence (BI) methods and functionality being built in to Excel (Tables, Power Query, Power Pivot)

Good luck.

 

[sts8500man]

Col,

Thank you for your wise and detailed consultation with me. I will take it from here. I don't look forward to jumping on a steep learning curve at 74 years old and already stressed for time utilization, but I have done it before. There is no reason to believe I can't do it now. I like keeping my brain active as I refuse to stagnate as I get older.

Thank you again. Signing off this thread.

sts8500man in Louisiana, USA

Take baby steps!! You can only eat an elephant one bite at a time.
Start slowly with small applications that don't involve using VBA (e.g. as a VLOOKUP table or as a Source Range for a Data Validation option list that may need to be added to over time) until you get the hang of how they work, how the automatic replication of formulas work, and how to write formulas that reference data within the table (where the formula is located both within and outside the table)


If you know the size of the import data you can insert the rquired number of blank rows and then paste the data into those, but why do you need data to go into the top row of the table? Is there not some key field that you can use to find the required rows within the table no matter where they are located? Or is it possible to assign a unique index number or date to each batch of imported data that will allow you to sort the table?


I'm not aware [but have never looked!) of any "wizard" that will clean up / convert VBA code that acts on a range to act on an Excel Table. As mentioned, Tables are a different animal (List Objects rather than Range objects), so whilst the end goal (what you want to do with the data) is still the same it's how the code goes about it is different (i.e. the references to the target area, and the identification of the Properties of those objects, and the Methods to manipulation them are different.)


I understand your position, especially if there is any size, complexity or criticality in your existing application - I had a similar issue some time ago. Having said that, as you learn about tables, you may get to the point where it's not such a big deal to convert. Also be aware that much of what we did in the past with data ranges and the manipulation thereof is being rapidly overtaken by the new database and business intelligence (BI) methods and functionality being built in to Excel (Tables, Power Query, Power Pivot)

Good luck.