Best Practice for VBA

[vikas_newports]

Hello
I am new to VBA and I am learning myself at my pace, after many days of working I created a macro/code to automate my daily task but unfortunately, it was rejected by my Boss
He Said you used filenames in your code if someone changes the file names will you code work?
Moreover, he said, you are using the main folder for all types of data (raw/source data and Helping data)
So I am going to rewrite the code. Is it fruitful to re-write or should I convince him for my Actual work?
How can I handle the errors

 

[mumps]

It is difficult to give you advice without seeing your code. Can you post a copy?

 

[vikas_newports]

it is more than 300+ lines code

 

[mumps]

I'm willing to have a look at it although I can't promise a solution.

 

[joeu2004]

Hello
I am new to VBA and I am learning myself at my pace, after many days of working I created a macro/code to automate my daily task but unfortunately, it was rejected by my Boss
He Said you used filenames in your code if someone changes the file names will you code work?
Moreover, he said, you are using the main folder for all types of data (raw/source data and Helping data)
So I am going to rewrite the code. Is it fruitful to re-write or should I convince him for my Actual work?
How can I handle the errors

No one but you can answer "is it fruitful to rewrite?". And "how can I handle errors?" is much too general.

The following is how I allow users to specify input and output file names. Caveat: It will overwrite an existing output file.

Dim fdIn As Long, fdOut As Long, path As String

On Error Resume Next
path = Application.GetOpenFilename(Title:="Open existing INPUT file")
If path = "" Or path = "False" Then Exit Sub
On Error GoTo 0
fdIn = FreeFile
Open path For Input Access Read As #fdIn

On Error Resume Next
path = Application.GetSaveAsFilename("", Title:="Open new OUTPUT file")
If path = "" Or path = "False" Then Exit Sub
On Error GoTo 0
fdOut = FreeFile
Open path For Output Access Write As #fdOut

 

[vikas_newports]

I used too much File names for almost every task like copy paste Filter ,formulas etc .
In my opinion its very hard to avoid filenames for working as we need to copy or filter data from different workbooks and extract some filterered data to save a new workbooks .. I think source data files names are issue and the Macro file name is too .

 

[Sulprobil]

You might be able to convince your boss if you structure your data and if you change your code from using absolute pathnames to relative ones.

Create subfolders for raw data, temp data, and reports. Use ThisWorkbook.Path to make pathnames relative to the program's location. Use filename suffixes like 20201115 to easily deal with input data and with output data over time.