Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Variables Twice?
Hi
Can the variables be declared in a more efficient manner in this case. The below code gets the job done but I was wondering if there was a better way than to Declare the names twice. Maybe some sort of loop through the name ranges. Just want to know how to clean this puppy up? Thanks in advance. Chad Sub BookExists() 'Test to see if a Workbooks exist Dim FindFile As String Dim BookName(2) As String, BookName2 As String Dim LPosition(2) As Integer Dim Myrng(2) As String Dim Mylen(2) As Long Dim Lcount(2) As Long Myrng(1) = Sheets("Data").Range("A110").Value Myrng(2) = Sheets("Data").Range("A111").Value LPosition(1) = InStrRev(Myrng(1), "\") LPosition(2) = InStrRev(Myrng(2), "\") Mylen(1) = Len(Myrng(1)) Mylen(2) = Len(Myrng(2)) Lcount(1) = Mylen(1) - LPosition(1) Lcount(2) = Mylen(2) - LPosition(2) BookName(1) = Right(Myrng(1), Lcount(1)) BookName(2) = Right(Myrng(2), Lcount(2)) FindFile = Range("BkPath").Value & "\Final Reports" With Application.FileSearch .LookIn = FindFile '* represents wildcard characters .Filename = BookName(1) .Filename = BookName(2) If .Execute 0 Then 'Workbook exists 'Am doing stuff here Else 'There is NOt a Workbook MsgBox BookName(1) & " Does not exist - Please Create File" MsgBox BookName(2) & " Does not exist - Please Create File" End If End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Variables Twice?
It's not declaring them, that's the Dim statements. It's assigning values
that you're talking about. And you can do something like this: For iCounter = 1 To 2 LPosition(iCounter) = InStrRev(Myrng(iCounter), "\") Mylen(iCounter) = Len(Myrng(iCounter)) Lcount(iCounter) = Mylen(iCounter) - LPosition(iCounter) Next - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Chad" wrote in message ... Hi Can the variables be declared in a more efficient manner in this case. The below code gets the job done but I was wondering if there was a better way than to Declare the names twice. Maybe some sort of loop through the name ranges. Just want to know how to clean this puppy up? Thanks in advance. Chad Sub BookExists() 'Test to see if a Workbooks exist Dim FindFile As String Dim BookName(2) As String, BookName2 As String Dim LPosition(2) As Integer Dim Myrng(2) As String Dim Mylen(2) As Long Dim Lcount(2) As Long Myrng(1) = Sheets("Data").Range("A110").Value Myrng(2) = Sheets("Data").Range("A111").Value LPosition(1) = InStrRev(Myrng(1), "\") LPosition(2) = InStrRev(Myrng(2), "\") Mylen(1) = Len(Myrng(1)) Mylen(2) = Len(Myrng(2)) Lcount(1) = Mylen(1) - LPosition(1) Lcount(2) = Mylen(2) - LPosition(2) BookName(1) = Right(Myrng(1), Lcount(1)) BookName(2) = Right(Myrng(2), Lcount(2)) FindFile = Range("BkPath").Value & "\Final Reports" With Application.FileSearch .LookIn = FindFile '* represents wildcard characters .Filename = BookName(1) .Filename = BookName(2) If .Execute 0 Then 'Workbook exists 'Am doing stuff here Else 'There is NOt a Workbook MsgBox BookName(1) & " Does not exist - Please Create File" MsgBox BookName(2) & " Does not exist - Please Create File" End If End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Variables Twice?
Hi Jon
Thanks for your reply. It worked a treat. I am very appreciative of your help, thankyou. I have looked at your website a great deal over the years and it has been more than helpful. It is a fantastic site and you should be very proud. Take care Chad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declaring Variables | Excel Programming | |||
Declaring Variables | Excel Programming | |||
DEclaring variables | Excel Programming | |||
Declaring variables | Excel Programming | |||
DEclaring variables | Excel Programming |