Question regarding Case Statement 1

[RichardOneil]

Hi-

This is a finesse question. I have code that pulls data from our hr database and writes it to a text file so that if can imported into a scheduling system. Due to a number of factors, I can't pull by corp/dept, I have to pull the data by a change date and then filter out only those records that will be loaded into the scheduling system. Our corporation has hundreds of depts, but only about 50-60 depts will be put be using the scheduling system.

I ended using a Case Statement:

Send = 0
Select Case DeptNo
Case "20.6010"
Send = 1
Case "30.6010"
Send = 1
End Select

If Send = 1 Then
*Code for Sending data to File
End If

I'm pretty much a self-taught newb when it comes to VBA and I am wondering if there is a more efficient/elegant way of accomplishing this? Thanks for the input.

 

[Andrzejek]

What about:

Send = 0
Select Case DeptNo
  Case "20.6010", "30.6010", 
      "40.0010" To "50.9999"
      Send = 1
End Select

If Send = 1 Then
   *Code for Sending data to File
End If

Have fun.

---- Andy

 

[Bong]

I like select case. One thing it does let you do is combine multiple cases as in case a, b, c, so you could simplify a little with:

select case DeptNo
  case "20.6010", "30.6010"
     <code for sending>
end select

_________________
Bob Rashkin

 

[Bong]

caught while I was on the phone!

_________________
Bob Rashkin

 

[Andrzejek]

You can also move Send = 0

Select Case DeptNo
  Case "20.6010", "30.6010", 
      "40.0010" To "50.9999"
      Send = 1
  [b]Case Else
      Send = 0[/b]
End Select

If Send = 1 Then
   *Code for Sending data to File
End If

Have fun.

---- Andy

 

[mintjulep]

Select Case DeptNo
  Case "20.6010", "30.6010",
      "40.0010" To "50.9999"
      SendFunction ()
End Select

Public sub SendFunction()
   *Code for Sending data to File
End Sub

 

[RichardOneil]

Thanks for the help. I will these suggestions out and see how they work.

 

[stevexff]

Hard coding the dept numbers in the program like this will give you continual ongoing maintenence problems, especially if you have hundreds as you state.

Due to a number of factors, I can't pull by corp/dept

Why not? How are you getting the data out of the database?

Perhaps if we could understand a bit more, we might be able to solve the problem, rather than working around the symptom?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]