code line number

Is there a way to retrieve the line number of the vba code itself? Such as,
if an error occurs, throwing up a message box that says "Error at code line
173"

Tony
Wed Feb 27 11:14:39 PST 2008

If you have line numbers in your code (not common these days) you could try
looking at Information.Erl

--
Enjoy,
Tony

"GrannyM" <GrannyM@discussions.microsoft.com> wrote in message
news:AAD11EAB-027B-4E19-8F34-2854E1F5AE8F@microsoft.com...
> Is there a way to retrieve the line number of the vba code itself? Such
> as,
> if an error occurs, throwing up a message box that says "Error at code
> line
> 173"

GrannyM
Wed Feb 27 13:21:44 PST 2008

We are using Word 2003. In the VBA module on the Standard Toolbar, it gives
you the line and col number of where your cursor is at in your code. We
would like to be able to pull that line number into a variable. To give you
a basic idea of what we want to do, we have hundreds of users and they expect
us to be omniscient and know everything without them giving us any
information. We've tried creating message boxes, but they tend to enter
right through them and then send us an email saying they had an error. We
would like to create error trapping that will create an error log for our IT
Department that would give us some information to help track down the error.
for example, something like: "UserName running macro 10-123 generated Error
Description at line #XX. Variables at the time of the error were: Variable
a = "1111", variable b = "", variable c = "abc".

"Tony Jollans" wrote:

> If you have line numbers in your code (not common these days) you could try
> looking at Information.Erl
>
> --
> Enjoy,
> Tony
>
> "GrannyM" <GrannyM@discussions.microsoft.com> wrote in message
> news:AAD11EAB-027B-4E19-8F34-2854E1F5AE8F@microsoft.com...
> > Is there a way to retrieve the line number of the vba code itself? Such
> > as,
> > if an error occurs, throwing up a message box that says "Error at code
> > line
> > 173"
>
>

Tony
Wed Feb 27 15:52:53 PST 2008

You can get that information if the users have "Allowed programmatic access
to the VBA project" (or whatever the option is called) but if they haven't
you will just generate another error.

You can't easily write a generic error trap that includes variables the way
you suggest (almost a mini dump) and if you are going to write specific
error traps and write a log with specific variables you probably have enough
information to hand without it.

--
Enjoy,
Tony

"GrannyM" <GrannyM@discussions.microsoft.com> wrote in message
news:57B554BC-5B15-4E09-B8BE-5AB1CD15C521@microsoft.com...
> We are using Word 2003. In the VBA module on the Standard Toolbar, it
> gives
> you the line and col number of where your cursor is at in your code. We
> would like to be able to pull that line number into a variable. To give
> you
> a basic idea of what we want to do, we have hundreds of users and they
> expect
> us to be omniscient and know everything without them giving us any
> information. We've tried creating message boxes, but they tend to enter
> right through them and then send us an email saying they had an error. We
> would like to create error trapping that will create an error log for our
> IT
> Department that would give us some information to help track down the
> error.
> for example, something like: "UserName running macro 10-123 generated
> Error
> Description at line #XX. Variables at the time of the error were:
> Variable
> a = "1111", variable b = "", variable c = "abc".
>
> "Tony Jollans" wrote:
>
>> If you have line numbers in your code (not common these days) you could
>> try
>> looking at Information.Erl
>>
>> --
>> Enjoy,
>> Tony
>>
>> "GrannyM" <GrannyM@discussions.microsoft.com> wrote in message
>> news:AAD11EAB-027B-4E19-8F34-2854E1F5AE8F@microsoft.com...
>> > Is there a way to retrieve the line number of the vba code itself?
>> > Such
>> > as,
>> > if an error occurs, throwing up a message box that says "Error at code
>> > line
>> > 173"
>>
>>

Karl
Wed Feb 27 16:07:00 PST 2008

GrannyM wrote:
> We are using Word 2003. In the VBA module on the Standard Toolbar, it gives
> you the line and col number of where your cursor is at in your code. We
> would like to be able to pull that line number into a variable.

As Tony said, unless you add numbers to each line, no can do.

> To give you
> a basic idea of what we want to do, we have hundreds of users and they expect
> us to be omniscient and know everything without them giving us any
> information.

"Doesn't work," right?

> We've tried creating message boxes, but they tend to enter
> right through them and then send us an email saying they had an error.

You could write a routine that timed how long the message box was up, and if they
dismissed it in under, say, a second, then pop it right back up there but double the
mandatory visible time. Could be kind of a fun exercise in dealing with unruly
users, actually. So fun, I just couldn't resist. <eg>

Public Sub InTheirFace(ByVal TheMsg As String, ByVal MinSecs As Double)
Dim Popped As Double
Const OneSec As Double = 1 / 86400
Do
If Popped <> 0 Then MinSecs = MinSecs * 2
Popped = CDbl(Now)
MsgBox TheMsg, vbCritical, "In Your Face!"
Loop Until (Popped + (OneSec * MinSecs)) < CDbl(Now)
End Sub

You could call it with something like:

Call InTheirFace("Report error code XYZ, luser!", 2)

> would like to create error trapping that will create an error log for our IT
> Department that would give us some information to help track down the error.
> for example, something like: "UserName running macro 10-123 generated Error
> Description at line #XX. Variables at the time of the error were: Variable
> a = "1111", variable b = "", variable c = "abc".

Well, as Tony said, if you want the errorline, you'll need to supply it.
--
.NET: It's About Trust!
http://vfred.mvps.org

Tony
Wed Feb 27 16:11:34 PST 2008

I really like that idea Karl!!

--
Enjoy,
Tony

"Karl E. Peterson" <karl@mvps.org> wrote in message
news:O8IDY3ZeIHA.1164@TK2MSFTNGP02.phx.gbl...
> GrannyM wrote:
>> We are using Word 2003. In the VBA module on the Standard Toolbar, it
>> gives
>> you the line and col number of where your cursor is at in your code. We
>> would like to be able to pull that line number into a variable.
>
> As Tony said, unless you add numbers to each line, no can do.
>
>> To give you
>> a basic idea of what we want to do, we have hundreds of users and they
>> expect
>> us to be omniscient and know everything without them giving us any
>> information.
>
> "Doesn't work," right?
>
>> We've tried creating message boxes, but they tend to enter
>> right through them and then send us an email saying they had an error.
>
> You could write a routine that timed how long the message box was up, and
> if they dismissed it in under, say, a second, then pop it right back up
> there but double the mandatory visible time. Could be kind of a fun
> exercise in dealing with unruly users, actually. So fun, I just couldn't
> resist. <eg>
>
> Public Sub InTheirFace(ByVal TheMsg As String, ByVal MinSecs As Double)
> Dim Popped As Double
> Const OneSec As Double = 1 / 86400
> Do
> If Popped <> 0 Then MinSecs = MinSecs * 2
> Popped = CDbl(Now)
> MsgBox TheMsg, vbCritical, "In Your Face!"
> Loop Until (Popped + (OneSec * MinSecs)) < CDbl(Now)
> End Sub
>
> You could call it with something like:
>
> Call InTheirFace("Report error code XYZ, luser!", 2)
>
>> would like to create error trapping that will create an error log for our
>> IT
>> Department that would give us some information to help track down the
>> error.
>> for example, something like: "UserName running macro 10-123 generated
>> Error
>> Description at line #XX. Variables at the time of the error were:
>> Variable
>> a = "1111", variable b = "", variable c = "abc".
>
> Well, as Tony said, if you want the errorline, you'll need to supply it.
> --
> .NET: It's About Trust!
> http://vfred.mvps.org
>

Karl
Wed Feb 27 16:33:30 PST 2008

Tony Jollans wrote:
>I really like that idea Karl!!

My old friend Alan Cooper would be rolling over in his grave, were he not still
alive. <lol>

>> Public Sub InTheirFace(ByVal TheMsg As String, ByVal MinSecs As Double)
>> Dim Popped As Double
>> Const OneSec As Double = 1 / 86400
>> Do
>> If Popped <> 0 Then MinSecs = MinSecs * 2
>> Popped = CDbl(Now)
>> MsgBox TheMsg, vbCritical, "In Your Face!"
>> Loop Until (Popped + (OneSec * MinSecs)) < CDbl(Now)
>> End Sub
--
.NET: It's About Trust!
http://vfred.mvps.org

GrannyM
Thu Feb 28 05:06:00 PST 2008

Thanks guys. That really does sound like fun. I can just think of so many
things I'd like to put into that second message box that would probably get
me into a lot of trouble - if they actual read the box!

"Karl E. Peterson" wrote:

> GrannyM wrote:
> > We are using Word 2003. In the VBA module on the Standard Toolbar, it gives
> > you the line and col number of where your cursor is at in your code. We
> > would like to be able to pull that line number into a variable.
>
> As Tony said, unless you add numbers to each line, no can do.
>
> > To give you
> > a basic idea of what we want to do, we have hundreds of users and they expect
> > us to be omniscient and know everything without them giving us any
> > information.
>
> "Doesn't work," right?
>
> > We've tried creating message boxes, but they tend to enter
> > right through them and then send us an email saying they had an error.
>
> You could write a routine that timed how long the message box was up, and if they
> dismissed it in under, say, a second, then pop it right back up there but double the
> mandatory visible time. Could be kind of a fun exercise in dealing with unruly
> users, actually. So fun, I just couldn't resist. <eg>
>
> Public Sub InTheirFace(ByVal TheMsg As String, ByVal MinSecs As Double)
> Dim Popped As Double
> Const OneSec As Double = 1 / 86400
> Do
> If Popped <> 0 Then MinSecs = MinSecs * 2
> Popped = CDbl(Now)
> MsgBox TheMsg, vbCritical, "In Your Face!"
> Loop Until (Popped + (OneSec * MinSecs)) < CDbl(Now)
> End Sub
>
> You could call it with something like:
>
> Call InTheirFace("Report error code XYZ, luser!", 2)
>
> > would like to create error trapping that will create an error log for our IT
> > Department that would give us some information to help track down the error.
> > for example, something like: "UserName running macro 10-123 generated Error
> > Description at line #XX. Variables at the time of the error were: Variable
> > a = "1111", variable b = "", variable c = "abc".
>
> Well, as Tony said, if you want the errorline, you'll need to supply it.
> --
> ..NET: It's About Trust!
> http://vfred.mvps.org
>
>
>

Karl
Thu Feb 28 12:55:19 PST 2008

GrannyM wrote:
> Thanks guys. That really does sound like fun. I can just think of so many
> things I'd like to put into that second message box that would probably get
> me into a lot of trouble - if they actual read the box!

That's the spirit! <LOL>
--
.NET: It's About Trust!
http://vfred.mvps.org