Oliver has attended:
Excel VBA Intro Intermediate course
Assigning a dimension
Hi,
I've recently changed PC's and it seems that I am now required to populate ".xls" when assigning an excel spreadsheet as dimension in code. For example, on my old computer, in code when i have two excel spreadsheets open and previously assigned "book1" as a dimension "D_Filename" and used the command;
Windows(D_Filename).Activate
the "book1" spreadsheet is activated.
Now I am required to assign "book1.xls" as the dimension, otherwise I receive "Run-time error `9`: Subscript out of range".
I am using the same version of Excel and VBA and cannot understand why the code doesn't work without ".xls" on my new computer - Are you able to assist?
Many thanks,
Oliver
RE: Assigning a dimension
Hi Oliver,
I'm not sure why that would be.
Perhaps it's to do with a Windows setting. Eg. I do know you can change your file view settings to 'hide file extensions for known file types', so perhaps it's that or some other Windows setting.
Is it not possibly to retype your code with the .xls part of the filename? Or are there many places you need to do this?
Another option is to assign a variable with value ".xls" and append this to all of your filenames. Then, if you are ever giving code to another colleague/computer and it requires the ".xls" part to NOT be there, you just unassign that one variable, effectively appending 'nothing' to all your filenames throughout all your code.
I hope this gives you some help. Sorry I can't offer any more explanation. Perhaps Microsoft can help?
Regards, Rich
