Check range before pasting and modular approach questions
 

I am writing some utilities which I plan to bundle together in an add-in for
coworkers. Some of these utilities involve pasting information in
user-selected cells and others involve placing formulas in cells based on
userform input. In either case, I want to check the range prior to
pasting/inserting formulas.

Here is what I am planning to check and how:
- Is the selection a range?
- Is the selection a continuous range (using .Areas)?
- Does the selection contained locked/protected cells (using .AllowEdit)?
- Does the selection contain array formulas (using .HasArray)?
- Does the selection already contain something (using
WorksheetFunction.CountA)?

My questions:
1. Are there additional checks I need to make or any suggestions on the
methods I mentioned?
2. How should I 'arrange' these functions? I could create functions for
each of the checks, then call them from my main subroutine, I could create
one function with all of these checks and call that from my subroutine, or I
could create a function that calls on individual functions and call the one
function from my subroutine. I was planning to use the first option but as
I have just started 'modularizing' my code (versus the fourth option of
putting everything in the subroutine, which is what I used to do) I would
appreciate the opinion of others.

Sorry for the long post. Thanks in advance for your help.

Check range before pasting and modular approach questions
 

Hi,

I think you want to build a utility for your Appl. Excel ?

Keep tryin' of it by create one by one of your module

Or you can see J-Walk website ... that will give u much

Rgds,

Halim


M. Authement menuliskan:
I am writing some utilities which I plan to bundle together in an add-in for
coworkers. Some of these utilities involve pasting information in
user-selected cells and others involve placing formulas in cells based on
userform input. In either case, I want to check the range prior to
pasting/inserting formulas.

Here is what I am planning to check and how:
- Is the selection a range?
- Is the selection a continuous range (using .Areas)?
- Does the selection contained locked/protected cells (using .AllowEdit)?
- Does the selection contain array formulas (using .HasArray)?
- Does the selection already contain something (using
WorksheetFunction.CountA)?

My questions:
1. Are there additional checks I need to make or any suggestions on the
methods I mentioned?
2. How should I 'arrange' these functions? I could create functions for
each of the checks, then call them from my main subroutine, I could create
one function with all of these checks and call that from my subroutine, or I
could create a function that calls on individual functions and call the one
function from my subroutine. I was planning to use the first option but as
I have just started 'modularizing' my code (versus the fourth option of
putting everything in the subroutine, which is what I used to do) I would
appreciate the opinion of others.

Sorry for the long post. Thanks in advance for your help.

Check range before pasting and modular approach questions
 

The Use rForm input is the preferred method for accuracy. If you have to
allow a user to input directly to the spreadsheet, I would suggest using a
separate sheet from which you can edit and then transfer the data into the
main spreadsheet. However, with the edits you have planned, most of the
drawbacks are covered.

As for the code structure, I prefer to modularize as much as possible and
where practicable. It seems to run better and if there is a problem, you can
usually pin point it pretty easily. However, each code writer has their own
likes and dislikes. I also use the indent system so I can easily identify
what is embedded in what and make sure all my blocks and loops are complete.

Good Luck JLG

"M. Authement" wrote:

I am writing some utilities which I plan to bundle together in an add-in for
coworkers. Some of these utilities involve pasting information in
user-selected cells and others involve placing formulas in cells based on
userform input. In either case, I want to check the range prior to
pasting/inserting formulas.

Here is what I am planning to check and how:
- Is the selection a range?
- Is the selection a continuous range (using .Areas)?
- Does the selection contained locked/protected cells (using .AllowEdit)?
- Does the selection contain array formulas (using .HasArray)?
- Does the selection already contain something (using
WorksheetFunction.CountA)?

My questions:
1. Are there additional checks I need to make or any suggestions on the
methods I mentioned?
2. How should I 'arrange' these functions? I could create functions for
each of the checks, then call them from my main subroutine, I could create
one function with all of these checks and call that from my subroutine, or I
could create a function that calls on individual functions and call the one
function from my subroutine. I was planning to use the first option but as
I have just started 'modularizing' my code (versus the fourth option of
putting everything in the subroutine, which is what I used to do) I would
appreciate the opinion of others.

Sorry for the long post. Thanks in advance for your help.