Help: How to Find and Replace VBA Code with VBA Code?

[cznuk]

I have a module that I created to transfer data from one sheet to another and to do it by month.
So currently what I have to do when I reach the end of the month is erase the data and save as the next month.

For example: "Jan. Data" When it reaches February 1st, I will have to delete the data (so I can still keep the format of the page) and save as "Feb. Data".

I can do that with VBA code fairly simple, but what I would like to do is alter my code so that once it reaches the end of the month (which will be calculated using a counter), it will change "Jan" to "Feb" everywhere in my VBA code, so that I do not have to manually go and change it.

Is there a way to code a Find and Replace within my own VBA code?

[Nick72310]

By no means am I an expert but...
Make the month a variable.
Or use Month(Date), which gives you the number for the current month. For example, if it was September, the value would be 9.

I would really need to see your code to give a better answer. Hope this helps though.

[cznuk]

What I'm really trying to do is have a loop that automatically change the month in the code, since I save each month to a separate workbook, I have the workbook named differently per month. And since I move from worksheets, through my code I activate the workbook "Jan. Air Data", and would love it if after a certain number of counts I can get it to switch to "Feb. Air Data" throughout my entire code.




By no means am I an expert but...
Make the month a variable.
Or use Month(Date), which gives you the number for the current month. For example, if it was September, the value would be 9.

I would really need to see your code to give a better answer. Hope this helps though.

[Nick72310]

What does your code look like?

[GTO]

...but what I would like to do is alter my code so that once it reaches the end of the month ..., it will change "Jan" to "Feb" everywhere in my VBA code,... Is there a way to code a Find and Replace within my own VBA code?

Yes and here's a simple example:

In a new workbook, in the ThisWorkbook Module:


Option Explicit

Sub example()
Dim vbp As VBProject
Dim vbComp As VBComponent
Dim lLine As Long
Set vbp = ThisWorkbook.VBProject
Set vbComp = vbp.VBComponents("ThisWorkbook")

With vbComp
For lLine = (.CodeModule.ProcStartLine("example", vbext_pk_Proc) + (.CodeModule.ProcCountLines("example", vbext_pk_Proc))) To .CodeModule.CountOfLines
If .CodeModule.Find("Mary", lLine, 1, lLine, -1, True, True) Then
.CodeModule.ReplaceLine lLine, "'" & String(9, vbTab) & "Little Jack Horner sat in the corner..."
Exit Sub
End If
Next
End With

End Sub

Sub Fake()
' This procedure needed just because without it, the "example" sub's count of lines would include the below comments...
End Sub

' object Required. An object expression that evaluates to an object in the Applies To list.
' target Required. A String containing the text or pattern you want to find.
' startline Required. A Long specifying the line at which you want to start the search; will be set to the line of the match if one is found. The first line is number 1.
' startcol Required. A Long specifying the column at which you want to start the search; will be set to the column containing the match if one is found. The first column is 1.
' Mary had a little lamb, who's fleece was white as snow
' endline Required. A Long specifying the last line of the match if one is found. The last line may be specified as –1.
' endcol Required. A Long specifying the last line of the match if one is found. The last column may be designated as –1.
' wholeword Optional. A Boolean value specifying whether to only match whole words. If True, only matches whole words. False is the default.
' matchcase Optional. A Boolean value specifying whether to match case. If True, the search is case sensitive. False is the default.
' patternsearch Optional. A Boolean value specifying whether or not the target string is a regular expression pattern.
' If True, the target string is a regular expression pattern. False is the default.




That said, whilst another neat thing to learn how to do, I would suggest using another method.
Simply put:


If anyone else uses the workbook, they must have the "Trust Access..." option enabled, and by default, it is not.
If you overlook anything, it is of course possible to replace the wrong bit of code.



What I'm really trying to do is have a loop that automatically change the month in the code, since I save each month to a separate workbook, I have the workbook named differently per month. And since I move from worksheets, through my code I activate the workbook "Jan. Air Data", and would love it if after a certain number of counts I can get it to switch to "Feb. Air Data" throughout my entire code.

As I read it, you are using the month's short name in the filename anyways. You could look for "Jan", "Feb", etc in the filename and use that.

Or... simply use a cell in a hidden sheet to save the month name.

Or... and probably the simplest (for your use, not other users), simply use a public constant to store the string and change your code to reflect.

Hope that helps,

Mark

PS - to run the code as shown, set a reference in the newly created workbook to: Microsoft Visual Basic for Application Extensibility 5.3