Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Pedro Gilbert   on Dec 03 In MS Office Category.

  
Question Answered By: Agatha Miller   on Dec 03

Everyone seems to have helped you with your second question, so I'll tackle
your first.

The Comments are in line.


> I am a beginner  of VBA. Just learned it for two weeks.
>
> I have some problem  with my code  where I try to change  the
system  date
> from 11/5/2004 to 11-5-2004 cause I need that name to be saved as
filename  and excel  won't accept  "/" or "\" as filename.
>
> So I wrote:
> ==============================================================
> Sub filename()
>
> D = Date

To start with, you are using a date field rather than a string, this is not
a big issue because it will still work.

length  = Len(n)

You are trying to determine the length of the date by using the wrong
variable. "n" is undefined so your result will be zero.

You should use
Option Explicit
To require that all variables must be defined.

> Dim i As Integer
>
> If i < Length Then

This is a single pass, so will only handle one replacement. You should put
these statements inside a For loop.
For i = 1 to Length

' the code

Next i

The second problem is that i will have a value of zero which means the Left
function will return an empty string.

> If Left(D, i) = "/" Then
> Left(D, i) = "-"
> i = i + 1
> End If

If you had set i initially to 1 and the left character was a "/" it would
have worked, but when you tried the next character you would be comparing
two characters. The Left function will select characters from the left of
the string up to the ith character. So your comparisons for "06/11/04" would
be
""
"0"
"06"
"06/"
"06/1"
"06/11"
Rather than Left you should be using Mid(D,i,1)

> End If

This is not a loop, so you end up dropping out of the code without comparing
the next character.


> MsgBox n
>
> End Sub
> ================================================================
> But it is not replacing  "/" with "-". Why?
>
> Can anyone please help  me out here, please? Thanks.

When trying to understand VBA code, open the "locals" window and use F8 to
step through the code. If you are using functions like Left(D,i), add an
extra statement where you assign it to a variable. The variable will show up
in the "locals" window and you will know the current value.

Share: 

 

This Question has 3 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on How to convert 11/5/2004 into11-5-2004 Or get search suggestion and latest updates.


Tagged: