Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Code Cleaning Was: Re: Can't open excel file

  Asked By: Aysel    Date: Dec 10    Category: MS Office    Views: 692
  

This may just solve some of those absolutely random Excel (application)
crashes I've had
too many of while running my (amateur in more ways that one) VBA code.

Is there a "Debug.Print" remover? I use 'em like salt on pretzles.

Share: 

 

12 Answers Found

 
Answer #1    Answered By: Naba Malik     Answered On: Dec 10

Want code  to change/alter code?????????????????????

 
Answer #2    Answered By: Elizabeth Hughes     Answered On: Dec 10

 code  modifying code. The oldest no-no on the books.

Well, VBA puts formulas into cells - and therefore can change them.
Not much difference.

 
Answer #3    Answered By: Arlene Harvey     Answered On: Dec 10

Actually I do it all the time to remove debug  code. I've also written code
to sort the Dims at the top of procedures and delete the ones not used.

 
Answer #4    Answered By: Caroline Bowman     Answered On: Dec 10

This reply is in 2 parts:

A)I'd like to see how you did that code  to sort the
Dims . . . I won't exactly admit that I have all that many extraneous
variables . . . let's just say I have at least twice as many as I
need . . . it could be more "twice" than that, but I'm not tellin'
how much. Besides, I"m not sure I want to know . .. :)

B)I had no idea that "code modifying code" a "no-no" whether
old or new. Learn something new every day.

 
Answer #5    Answered By: Karla Ortiz     Answered On: Dec 10

I've put it all in a module so I'll export zip
and send it.

Can you send an email adress to shove it to please?

And you don't have to find out about unused by running  your own code.
There's a great set of VBA tools called MZ-Tools of which V3 is free.

And... IMHO.. Modyfying code  isn't such a big deal really. I know of some
programs for example that have a fixed data area.. ( another reason to put
data in one place maybe ) and to swap languages a block of memory is swapped
in.

Actually there is an issue with modifying VBA code but I'll tell yer when I
send the code. The most tricky thing I've tried to date is to define
vartiables "on the fly". I had a list of variables that I kept adding to and
didn't want to keep updating the code so I got the code to write a small
procedure that updated the dims and initialised the variable in situ so to
speak. It worked but I wasn't 100% happy with it.

I's like a lot of stuff I guess... If you know what you're doing then
anything can happen in the next half hour...

 
Answer #6    Answered By: Anu K     Answered On: Dec 10

you wrote >I know of some programs for example that have a fixed
data area.. ( another reason to put data in one place maybe ) and to
swap languages a block of memory is swapped in.>

That sounds like something I need to know more about . . . data
management is the name of the game at one level or another. Please
help me understand this more or head me in the right direction to
research it. Of course, the group would benefit most, since I'm sure
I'm not the only one who that would be relevant to--so I'll look for
your response. However, that might be a good topic for a new
thread. Thanks for your help, and I'll look forward to learning more.

 
Answer #7    Answered By: Fjodor Bonkob     Answered On: Dec 10

Another way of looking at it - don't DIM until the very last minute.

I'm not aware of any gain from putting DIMs all at the top of the code, and
there are at least two drawbacks IMHO:

1) is that you end up with unused variables, over time.

2) is that you have no "circumstantial" protection against using a variable
before it is populated with a value.

When I'm writing C++ (Java, JavaScript, etc), I'll normally declare the
variable in the same statement that I initialise it ...

int daysPerLeapYear = 366;

I use the colon construct in VBA to achieve almost the same result ...

Dim DaysPerLeapYear As Integer: DaysPerLeapYear = 366

In both cases, it is not possible for me to hit the declared variable and
have it not also initialised. The compiler throws out any reference to the
variable that isn't in the correct scope.

In cases when I must declare the variable somewhere else because of scope, I
declare it as close as possible to where it is set, to minimise the
likelihood of trying to use a variable that hasn't been populated with the
correct information.

(Sorry about the stupid example, but it's the syntax I'm talking about, and
my mind is blank at the moment.)

 
Answer #8    Answered By: Faizah Khan     Answered On: Dec 10

I think you have some good reasons to declare variables in that way... For
you.

A big advantage for me to declare at the top is to have them together so
that I *can* sort them and zap the unused. I also think it's neater... But
what the heck.

If you want to see a list of unused try out MZTools. A great set of VBA
tools with lots of options.... Well worth looking at and V3.0 is free.

I'd actually tend to declare DaysPerLeapYear as a constant... It aint gonna
alter for a loooong time, and prefix it with a type and sort of scope.

I try to use a 1, 2 or 3 character... Smallest I can without being
ambiguous... Type prefix along with a character specifying where it comes
from like "l" for local, "p" for parameter and "g" for global.

So "ilN" becomes a local integer counter and so on.

 
Answer #9    Answered By: Kawkab Mansour     Answered On: Dec 10

for the new perspective. I've been trying to write
tighter code, so if I can learn to do what you suggested, that would
help, I'm sure.

 
Answer #10    Answered By: Christina Ramirez     Answered On: Dec 10

Yes - very subjective. Same as I never use type prefixes on variables (like
"dbl", etc). I've seen just too many instances of people changing the
variable type but not changing the prefix - causing confusion.

> A big advantage for me to declare at the top is to have them together so
> that I *can* sort them and zap the unused. I also think it's neater... But
> what the heck.

Ah, you see, I don't have that problem. :-) If I remove the usage of a
variable, I also remove its declaration - because they are on the same line.

> I'd actually tend to declare DaysPerLeapYear as a constant... It aint
> gonna
> alter for a loooong time, and prefix it with a type and sort of scope.

Oh yes, so would I. As I said in my message, my mind was blank at the time
and the example was stupid.

But why use a type and scope prefix? The compiler will tell you if it's out
of scope and the type is immaterial. (Actually, I do use a prefix for
really global variables - a lower-case "g" - but I avoid that type of
variable as much as possible, anyway. Absence of that "g" denotes that it's
not defined in my global variables module and is therefore either private or
module-level public - in which case it would be used from elsewhere with dot
notation.)

I suppose my emphasis is on the readability of the flow of the algorithm in
the code, and I leave the respective compilers to tell me when I'm
mis-matching types or am out of scope. As an OO programmer from the
beginnings of OO, I also structure code  severely, and don't tend to have
lots of variables in subroutines or subroutines long enough to "lose"
definitions in.

Perhaps my pet hate in the impact on readability comes in the "C" type of
languages, where it is an old convention to declare constants using
upper-case letters and underscores in their names - as distinct from the
mixed upper/lower of variables. To me, this completely mucks up the
readability flow, for a gain of nothing. The compiler will tell you quickly
enough if you try to change a constant. (And, of course, if you then decide
it should be a variable, you need to do a global change to put its name into
the correct form. Or just leave it looking like a constant, which is what I
see more often.)

So. My _personal_ preference is not to encumber the code with things that
the compiler can handle just as well, but instead to concentrate on making
variable names as meaningful about their purpose as possible.

 
Answer #11    Answered By: Charlie Smith     Answered On: Dec 10

Only if it feels right though. My background is from FORTRAN, Ada, C++ -
rather than VB - so I have a different slant on things.

 
Answer #12    Answered By: Daniel Evans     Answered On: Dec 10

There's not really much for us to go on here, but...
to start at the very basic...
when you were adding code  to the web toolbar, where did you store it?
What is the name of the file?
if you're hiding file  extensions, looking at the file in an Explorer
window only shows the filename, not the extension.
One place toolbars are stored is in .xlt files.
The icon in explorer LOOKS like an excel  icon, because .xlt files are
for excel, but the act like add-ins and are not displayed.
(kind-of like your personal.xls file, but not quite. It is called up
each time you enter excel, but it doesn't get displayed either)

just for grins, try renaming the file to newfile.xls and see if you can
call it up...

again... this may not be the problem, but it IS a techniqe for creating
and distributing toolbars.

 
Didn't find what you were looking for? Find more on Code Cleaning Was: Re: Can't open excel file Or get search suggestion and latest updates.




Tagged: