Posts by neverland

I was making a form where the user could enter a number; eg. 4.61

However, depending on the user's background he might enter this as 4,61 (comma) or as 4.61

In the form this variable comes in as a string. How can I convert this in VBA to a digit if I don't know in advance if the users system uses a . or , as the digit-seperator??

Any help would be very grateful

I thought that in access you can actually predefine the type that is entered into the form but I don't believe this is possible in Excel (or at least i could not find something like that)

thanks for the help

---
<snip by admin. Read the rules you have agreed to or you may be banned>

I was looking for code that actually deletes ALL modules in the VBA code after I terminate a particular program.

Is there an easy way to do this?

I have some keys in a file that are of the form
WK05, meaning that it refers to the 5th calendar week (of this year). I would like to write a function that gives me the first day of this particular week, in this case it would be 28/1/2008

How can I do this?

Any help would be appreciated.

Re: Interrupt Update

It finished after 3 hours...

cool, finally.

About 2 hours ago I opened an excel file with external links (links to other excel files).

It asked me if it should update

Unfortunately I said yes

It is still opening as I am typing here.

The problem is that there is yet another file open in the same excel that has not been saved.

Is there any way of recovering the other file or interrupting excel?

I tried control+break but that does not work

By the way, the other file is actually an addin file with code.

Kind regards

Good morning,

I would like to activate/deactivate some reference through code. I mean the checkboxes you see when you go in VBA-editor to
Tools -> References

I see some items in this list and I want to be able to activate them or deactivate them through code.

Why?
The references refer to things I can only do on one certain PC wich contains a Bloomberg-link. But the same file is also used on other PC's once the links have been updated. At that time I don't need those references anymore and they give some problems because the reference cannot be found on the other pc

Thanks in advance

Re: My function got too long...

thanks for the help but storing in a column does not really work for me since all code is inside an addin.

But I just worked around it like this

sub cases_1(i)
select case i
 case 1
   msgbox "1"
 case 2
   msgbox "2"
 case 3
   msgbox "3"
...
 case else
  call cases_2(i)
 end select
end 


sub cases_2(i)
select case i
 case 1000
  msgbox "1000"
end select
end

or something like that. Anyway, thanks a lot for the help

Re: My function got too long...

It looks something like:
but there more than 2000 cases by now. And all growing. The new cases are generated automatically in the code, so combining them is not an option.

Function lots_of_cases(choice)


case select choice
case 1003
  lots_of_cases = "Something"


case 2004
 lots_of_cases = "Something else"


case 4543
 lots_of_cases = "Yet something else"


end select
end function

Perhaps I can stop and add a "otherwise select" and refer to another function and start there all over...

Re: My function got too long...

I forgot to say, it is VBA code

thanks for the help

Hi guys,

I have discovered something bad within my code.

I have a function that contains a lot of Cases (Select Case). Each month there are about 20 new cases.

But now I have run into a problem.

<COMPILE ERROR: FUNCTION TOO LONG>

Is there anything that can be done about it? Or do I have to rewrite the function into several functions?

Many thanks

Re: Slow performance

I could post the code but it's rather long.

The first file contains a lot of information. Some lines have to be skipped, others have to be added and combined into new ones.

It's a bit hard to explain, but 15 minutes is perfect for me. It used to take 4 hours to maintain the second file by hand. Now it not maintained anymore, but build from scratch.

Thanks for the help anyway

Hi guys,

I have just finished creating a large macro to create a new file I need every two weeks.

In order to speed up the performance, I added

ScreenUpdate:= False

But my macro took more than 5 hours to finish only partly.

Then I replaced it by

screenupdate:=True

and now it takes about 15 minutes...

I thought this screenupdate was supposed to speed things up? Without it, you can actually see the program switch between two excel-files. Is has to read information from one, and create formules and stuff in the second.

Any explanations?

Kind regards

Peter

In VBA I want to access the first line following the last filled cell.

I use Range("A1:I" & Range(A65536).End(xlUp)).Select

to go to the last filled line. But now I realise I don't know how to go the next line.

Can anyone help me out?

Re: Split worksheet function

Quote from Andy Pope

Do you mean like Text to Columns?

No I meant

ABC - CODE - 1002

has to be converted to

ABC 1002

Hi Guys,

I know that in VBA you can split a string using the SPLIT-function

But can you do the same in Excel without entering into a macro?

Greetings

Re: Vlookup feature

perfect!!

Hi,

I have this file eg

Hello 3
test 11
never 12
old 10

And I would like to find the matching value for 11. Vlookup (11, Range,...) does not work since the key needs to be in the first column and nog in the second.

changing the file is not an option. What can I do?

Kind regards

Peter

Hello,

I am looking for a VBA-way to check whether a certain string contains the other string.

Eg:
string1 = "Hello World"
string2 = "Hello everybody"

teststring = "orld"

String1 contains the teststring, string2 does not.

Is there an easy way?

I was playing around with the worksheetfunction find but this gives an error if the teststring is not found

Kind regards

Peter

Re: Replace links - original file has moved

many thanks!!!

Hi,

I have a lot of files that contain links to other files.
C:\old_excel\file1.xls
C:\old_excel2\file2.xls
C:\old_excel3\file3.xls

Now I moved these files to
C:\new_excel\file1.xls
C:\new_excel2\file2.xls
and I removed file3 (see below)

The links to file1 and file3 are basic links, eg:

[INDENT]='C:\old_excel\[file1.xls]Sheet1'!$A$1[/INDENT]

while the links to file2 are links to functions, eg:

[INDENT]='C:\old_excel2\file2.xls'!function1(10)[/INDENT]

I would like to have a subroutine that

• opens all files in a directory
• scans each sheet for links to file1, file2 and file3 and do the following:
  [INDENT]
• links to file1 should be redirected to file1 in its new location
• links to file2 should be redirected to its new location
• links to file3 should be redirected to the same cell in the sheet "input" in the currentfile (so no external link anymore)[/INDENT]

Can anybody help me? I was doing this by hand but there are over 600 files...

I would be very grateful

Many thanks in advance