Scope issue Variable not defined 1

[vba317]

Recently I have moved as much code as I can into functions. The issue I am having is when I am calling the procedure from a form, whatever variables I am using come up as variable not defined. I add Dim statements in the form to remove trhe error message. Is there another way to declare a variable instead of having to dim it?

Tom

 

[PHV]

So, seems like you opted for Option Explicit.
To discover all undefined variables in your project, simply compile it.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

If you show the code in question, the help will be possible.
Otherwise we can only guess, but that’s not what you want.


Have fun.

---- Andy

 

[MajP]

Can you provide an example of a function and how you call it from a form.

 

[vba317]

I am still working on this function I cant get iCurRptMon to subtract 1 month from the current month and result with a 10.


Public Function CurRptMon(pd As Integer, strCurMonL As String, strCurMonS As String, iCurFiscalMon As Integer, iCurFiscalYr As Integer, iCurCalYr As Integer, iCurRptMon As Date, dtCurDate As Date) As Integer
Dim dtCurMon As Date
Dim iFiscalMon As Integer
Dim ipdCalc As Integer

dtCurDate = Date

iFiscalMon = iFMonthStart
iCurFiscalMon = GetFiscalMonth(dtCurDate) - 1
iCurFiscalYr = GetFiscalYear(dtCurDate)
iCurCalYr = Format(dtCurDate, "yyyy")
iCurRptMon = DateAdd("m", -1, dtCurDate)
iCurRptMon = Format(iCurRptMon, "mm")
'iCurRptMon = Format(DateAdd("m", -1, Date), "mmmm,yyyy")


strCurMonL = MonthName(iFiscalMon, False)
strCurMonS = MonthName(iFiscalMon, True)
ipdCalc = 369
pd = ipdCalc + iCurFiscalMon
End Function


Function that I am calling it from
Public Function ImportFile()
Dim strFileLoc As String
Dim strTbl1Name As String
Dim strTbl2Name As String
Dim strFileExt As String
Dim strCurMon As String
Dim pd As Integer
Dim strCurMonL As String
Dim strCurMonS As String
Dim iCurFiscalMon As Integer
Dim iCurFiscalYr As Integer
Dim iFiscalMon As Integer
Dim iCurCalYr As Integer
Dim iCurRptMon As Date
Dim dtCurDate As Date
Call CurRptMon(pd, strCurMonL, strCurMonS, iCurFiscalMon, iCurFiscalYr, iCurCalYr, iCurRptMon, dtCurDate)

strFileLoc = "M:\"
strFileExt = ".txt"
strTbl1Name = "NBM_CS_Data_" & iCurFiscalMon & iCurCalYr & "_" & iCurFiscalMon & iCurCalYr & strFileExt
strTbl2Name = "NBM_AR_Data_" & iCurFiscalMon & iCurCalYr & "_1" & strFileExt

End Function

 

[Andrzejek]

If you want iCurRptMon to be 10, you cannot declare it as Date, make it an Integer:
[tt]
Dim iCurRptMon As [blue]Integer[/blue]

iCurRptMon = Month(DateAdd("m", -1, Date))

Debug.Print iCurRptMon
[/tt]

Also, have [tt]Option Explicit[/tt] at the top of your code.

Have fun.

---- Andy

 

[PHV]

I think you want to replace this:
Call CurRptMon(pd,...
with this:
iCurRptMon = CurRptMon(pd,...

And, as outlined by Andy, iCurRptMon should be dimmed as Interger

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Andy,
I have taken option explicit out of both procedures. If I comment out the Dim statements in the Public Function ImportFile()I get a compile error ByRef argument type mismatch.

I changed the iCurRptMon to an integer. I now get a runtime '6' overflow. at the iCurRptMon = DateAdd("m", -1, Date) line

Tom

 

[Andrzejek]

[tt]
iCurRptMon = [red]Month[/red](DateAdd("m", -1, Date))
[/tt]

"I have taken option explicit out of both procedures" How?

Just have it at the top of your code. Or - better yet - check Tools - Options - Editor tab - Require Variable Declaration checkbox.

Have fun.

---- Andy

 

[vba317]

I tried PHV's solution and I still get a ByRef error on the new strCurRptMon statement.

Dim strCurRptMon As String
strCurRptMon = CurRptMon(pd, strCurMonL, strCurMonS, iCurFiscalMon, iCurFiscalYr, iCurCalYr, iCurRptMon, dtCurDate)

Tom

 

[Andrzejek]

You may also 'invest' in MZTools which in 2 clicks will tell you (and a LOT more):

Public Function CurRptMon(pd As Integer, strCurMonL As String, strCurMonS As String, iCurFiscalMon As Integer, iCurFiscalYr As Integer, iCurCalYr As Integer, iCurRptMon As Date, dtCurDate As Date) As Integer
[blue]The variable 'dtCurMon' is not used[/blue]
Public Function ImportFile()[blue]
The variable 'strCurMon' is not used
The variable 'iFiscalMon' is not used[/blue]

Have fun.

---- Andy

 

[vba317]

Andy,
I took out the option explicit which was at the top of both functions. I am still getting the ByRef error. I just tried your iCurRptMon = Month(DateAdd("m", -1, Date)) it works great.

Tom

 

[Andrzejek]

What PHV is saying is:
CurRptMon is a Function that returns a value, an Integer. So you cannot just Call it (like a Sub), you need to assign its returned value to something, prefably to another Integer

Have fun.

---- Andy

 

[PHV]

Anyway, why transform a Sub into a Function that don't return any value ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

PHV,
I did not suggest transforming a Function to a Sub.
I just stated if you have a Sub, you can just Call it, but if you have a Function, you should assigned its returned value to something, not just Call it. Just ‘re-worded’ what you showed in your post
I am sorry if I confused the subject :-(

Have fun.

---- Andy

 

[PHV]

Andy, my remark was for the OP, not you.

 

[MajP]

Not trying to be harsh, but this is fundamentally wrong in so many ways. You do not seem to understand the basics of passing arguments and returning values. Try reading

http://www.functionx.com/visualbasic/functions/Lesson2.htm

 

[Andrzejek]

This "ByRef error" you are getting when you put back Option Explicit....

Is it by any chance compiler saying: "I expect an Integer to be passed to this Function, but I am getting something else" (like a String)?

I see you do have some standards of naming your variables (nice):
[blue]str[/blue]CurMonL As [blue]String[/blue], [blue]str[/blue]CurMonS As [blue]String[/blue], [blue]i[/blue]CurFiscalMon As [blue]Integer[/blue], [blue]i[/blue]CurFiscalYr As[blue] Integer[/blue]
but also (confusing):
[red]???[/red]pd As [blue]Integer[/blue], [red]i[/red]CurRptMon As [red]Date[/red]
so it easy to be confused of what needs to be passed and what actually IS passed.

Option Explicit is your friend, not the enemy


Have fun.

---- Andy

 

[MajP]

These are also good.

http://www.cpearson.com/excel/writingfunctionsinvba.aspx

http://msdn.microsoft.com/en-us/library/aa263527(v=vs.60).aspx

http://www.homeandlearn.co.uk/net/nets9p6.html

 

[vba317]

Thanks to Andy I have fixed the iCurRptMon issue. I also read the materials suggested by Majp Thanks. I have also changed the variables in question to be more consistent. I have having an issue importing a text file should I contine on this thread or start a new one?