microsoft-access-courses - Assigning a dimension

Resolved · Low Priority · Version Standard

Fri 18 Jan 2008, 10:29 Delegate Oliver said...

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

Fri 4 Apr 2008, 09:43 Trainer Rich said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

The dual nature of toolbar buttons

Many toolbar buttons are dual purpose, though the two purposes are often linked in some way. For example, Align Left aligns a cell's contents to the left of the cell. However, hold down Shift and press the Align Left button: Excel aligns the cell contents to the right.
You may respond: So what? Well, you can reduce the number of buttons on your toolbar to make your screen less cluttered and allow more room for, perhaps, some of your own commands. After all, what's the point of an Align Right button when Shift+Align Left does the same thing?

View all Excel hints and tips