External:=True

J

Jac Tremblay

Hi,
I found many posts about the property and I still have a problem with one
statement that bugs because the active sheet is not the one the data is
supposed to be erased from.
rngDelete.Range(Cells(intIndex + 1, 1), _
Cells(intIndex + 1, intNbCol)).Value = ""
I get the error 1004.
When the right sheet is the active one, there is no problem with the code. I
tried to specify the parameter (External:=True) in different places, but I
allways get an error. I seems that it can only be specified after the Address
property.
What should do?
Can someone help me please?
 
J

Jac Tremblay

Hi Carlos,
Your point seems logical to me. I will try it right now and post another
answer later on. Thank you for this quick answer.
By the way, why do you use Call? Why not just xlRng.ClearContents?
Another point: I thought that xlRng.Value = "" was the same as
xlRng.ClearContents. Am I right? I do not want to delete the cells or clear
the formats or anything else, I just want the contents to become a null
string.
Thanks again for your answer. I will sleep better tonight.
 
J

Jac Tremblay

Hi Carlos,

I did try your code with my application and it works fine. You are a king.

Thanks again.
 
C

Carlos

Hi Jac,

1. Use of "Call". You use call (a) when you do not want to store the result
of a function; and (b) when you use parenthesis while writing the arguments
of a macro.

Example of (a). Suppose you've got the function f(x) = x+2. If you just want
to "call" it you say Call f(x), instead of saying y = f(x). This example is
overly simplistic, but I guess building complex examples is not the case here.

Example of (b). Suppose you've got a macro g(x,y). As you mentioned before,
you can say g x, y and it works, but I prefer to call the function in order
to make it clear what arguments does g uses, so I say Call g(x,y).

2. xlRng.Value = "". This differs from ClearContents in that "" is a value:
it is the null string. This value is even one of Excel's constants, the
vbNullString constant. When you say xlRng.Value = "", you are actually
assigning a value to the cells, while when you say xlRng.ClearContents you
are leaving the range blank (without values). By the way, the .Value property
is the default property of a range object, so you don't have to say
xlRng.Value = z, but just xlRng = z.

I know the distinction between vbNullString and ClearContents might not make
the difference for the vast majority of applications. My intention was to
point out that it is different. For example, try filling some cells with the
formula ="" in a blank worksheet. If you move with the keyboard using Ctrl
and the directional arrows (up, down, left and right), you will find that
Excel treats the cells with "" different from blank cells.
 
D

Dave Peterson

I use "Call" only when I'm calling a subroutine that I wrote. I don't usually
use it for my own functions. I'll never use it a builtin VBA function.

And the only difference I've seen between rng.clearcontents and rng.value = ""
is when there is a merged cell in the range. The .clearcontents won't work (as
written). Assigning the value will work.
 
J

Jac Tremblay

Hi Carlos,
I tested your version of my code and it only works when I just inserted some
data. I does not work on actual data (inserted before).
Here is the code with the problem.
' ***
With rngSuppr
..Range(.Cells(intIndex + 1, 1), _
..Cells(intIndex + 1, intNbCol)).Value = ""
End With
' ***
I patched the problem like thsi but I do not like that solution.
' ***
Dim intK As Integer
With rngSuppr
For intK = 1 To intNbCol
..Cells(intIndex + 1, intK).Value = ""
Next intK
End With
' ***
Would you be able to tell me what the problem is.
Thanks
 
Top