VBA Rubberducking

RubberDuck

02:04

Hey @Mat'sMug, You'll like this.

Since Rubberduck is a dll instead of an exe, app.config doesn't work. Looks like I get to homebrew this in the registry. =)

Tomorrow though. For now I need to reset my branch..... I made a mess. lol

RubberDuck

03:17

No @ducky, this might work yet. Using the reg is stupid. And if the built in config won't work, then parse the XML yourself.

Mat's Mug

meh.. at the end of the day it's just an xml file with a bunch of options. What's stopping us from ... doing that ^^

lol

and save it in %appdata%\roaming\Rubberduck

RubberDuck

Lol. Yeah. That's exactly what I'm going to do.

I am starting to understand why this has not been done before though. Roadblock at every turn.

Mat's Mug

"this" being... the whole add-in idea?

C# bounty expiring in 22 hours: codereview.stackexchange.com/q/69359/23788

(my 400th answer :)

RubberDuck

Yes.

Wow. That's awesome Mug.

Hmmm.... I might finally break down and learn some Linq... msdn.microsoft.com/en-us/library/…

Mat's Mug

oh yeah. If you're going to deal with XML, go with Linq-to-Xml.

or, actually... can't we just serialize/deserialize some Settings class?

RubberDuck

03:31

Serialize?

Mat's Mug

msdn.microsoft.com/en-us/library/…

RubberDuck

That's perfect. And I'm willing to bet System.Configuration is built on top of that.

Mat's Mug

might just be

to be serializable, members only need to be public.

RubberDuck

> Serialization of ArrayList and Generic List
The XmlSerializer cannot serialize or deserialize the following:
Arrays of ArrayList
Arrays of List<T>

Boooo

Mat's Mug

lol. ArrayList is obsolete since .net 2.0, ...and who would ever want an array of generic lists?

RubberDuck

03:39

Ohh. I missed the arrays part.

So long as I can serialize a list it's all good.

Mat's Mug

yup

RubberDuck

@duck's in over his head. =) I'm having fun though. It's been a long while since I really struggled like this. It's good for the brain.

Mat's Mug

welcome to .net :)

Mat's Mug

04:33

@RubberDuck just pushed 2 commits, fixes the CodeBlockNode child node types and the Instruction comments which were no longer being captured due to an earlier brainfart. Now all that's left to do is DRY up the Parser class - new ISyntax implementations can be added in about 10 minutes, if the regular syntax is simple (like, I'll implement OptionSyntax in no more than 5 minutes)

todo: traverse entire list of reserved keywords - make the parser recognize built-in functions

and TTGTB

vba4all

08:14

2

Q: Excel "From xml data import" won't add new columns

Excel 2010 has a fabulous feature under Data->"From Other Sources"->"From XML Data Import" to pull in XML from a web service, no coding required. Works great, but my service has now added a new column at the end. Refreshing the connection in Excel does not add the new column. In fact, creating a ...

2

A: Null values using Excel ADODB

Basically, don't rely on the registry entries as explained here on MSDN. You need to create a Schema.ini file and put it in the same folder as all your text files. In the Schema.ini you specify the type for all columns you may have in your text files - it's just a much safer option to do that e...

Mat's Mug

12:45

19.8!

RubberDuck

19.8 what?

@vba4all are you across the pond? You seem to be a few hours ahead of us.

vba4all

13:05

yep currently living in the UK

lived in the states for about 5 years

but moved to the UK about 5 years ago

so when its gets busy here I am already home - NOT Programming hahah

RubberDuck

13:17

kill it @ticker

vba4all

14:59

can you vote to close this (my) question please

-3

Q: What does a single 0 do in VBA code?

I have been looking all over the place but I can't find anything about what a single standalone 0 do in a sample VBA code. Sub Main() If True Then 0 End If End Sub What does the 0 do in here? What about this sample? Sub Main() If True Then 0 Else 0 End If End Sub ...

vba

RubberDuck

I don't have the rep cuz.

Why is that downvoted? Seems like a good question to me.

vba4all

don't know

i've became a target of some sort of revenge

been regulary getting downvotes since a few days ago :P

RubberDuck

This does what I think it does doesn't it?

0 GOTO 0 !!!! — stuartd Oct 25 '13 at 9:29

vba4all

haha

you wont know it til you try it :P

@RubberDuck maybe I got mr. pnuts angry stackoverflow.com/q/25547723/2140173

RubberDuck

Best infinite loop ever.

Public Sub TestLoop()
0 GoTo 0
End Sub

vba4all

15:10

maybe not best

but probably shortest ever syntax for a loop

RubberDuck

Looks like it's just stuck on that line (because it is).

vba4all

breaks a breakpoint while debugging ;P

RubberDuck

For the records, I'm with you about that formula questions. (most formula questions actually). They belong on Super User. There's enough crap on SO.

vba4all

yeah but people whos entire reputation is based off answering =SUM() and COUNTIF() type of questions aren't happy with that view. I can understand that but seriously, formulas should be tied to SU not SO.

RubberDuck

Right, so they can go earn a ton of rep on SU....

vba4all

15:21

An average asked / super user / super Excel user doesn't have a clue you can actually program in Excel using VBA...

what does his question do on a programming site then..

RubberDuck

The average SO vba question doesn't have a clue that they can actually program in Excel using VBA.

vba4all

haha

true

RubberDuck

=;)-

vba4all

yo

2k reputation for THAT

wow...

Rubber, please ask HOW TO WRITE THE SHORTEST LOOP IN VBA

and Ill answer with 0 GOTO 0

easy 2K for both

RubberDuck

LOL

vba4all

15:26

hahah

RubberDuck

That might make for an interesting PPG question actually.

CodeGolf, shortest infinite loop.

Sub
0 Goto 0
End

vba4all

hey

the best part is this

RubberDuck

18 bytes

vba4all

.... 2 years later

RubberDuck

And it got 9 upvotes....

I've often wondered how you do that cool button notation though.

vba4all

15:29

<kbd>BUTTON</kbd>

if you don't know just EDIT the answer/question and look at the source

RubberDuck

@vba4all I've not seen one IRL yet to do that. It always seems to be a screenshot.

<kbd>button</kbd>

Obviously doesn't work in chat. lol

vba4all

there

33

A: Bypass password on an Excel VBA project *.xla

Steps to reproduce Open your EDA.xla file in Notepad++ and hit CTRL+F to search through the file. Type in DPB as the search term. Once the term is found replace the DPB with DPx Save and close Open the EDA.xla now. You should get a warning msg » hit YES Open the VBE ALT+F11 or turn o...

CTRL + F

<kbd>CTRL</kbd>+<kbd>F</kbd>

RubberDuck

That's cool. I want to click the buttons....

vba4all

thats what snippets are for :P

you can make clickable things etc

RubberDuck

I'm going to learn javascript just to write snippets to have reviewed on CR.

vba4all

15:54

@RubberDuck don't miss the 0.005M reputation points

7

Q: Passing a 1D array (single column of a Range object) to .NET to manipulate it without loops and returning it back to VBA

Background Kind of a follow up - slightly related to my other SO question. I thought that if somehow I find a way in VBA to pass a single column to .NET and convert it to a native .NET type then I could finally overcome the mystery of explicitly looping on objects via COM (which is super slow a...

c# optimization array vba com

RubberDuck

@vba4all I saw it. And I've still got nothing.

Phrancis

lurks...

2

RubberDuck

Hey @Phrancis.

I'm actually out for a little bit guys. Some jerk shceduled a 11:30 - 12:30......

bbiab

Phrancis

That's a crappy time for a meeting. Hope they're buying lunch.

vba4all

yo @Phrancis

RubberDuck

16:14

@Phrancis I'm remote. The office is in PA and I'm in OH.

So, no. They're not buying lunch. =(

-1

Q: Excel VBA - Skip sub or end sub IF Statement

My code loops through cell values in a table and sees if document with same name exists. If it does exist, it will perform an action to open those files and import data. If bProcess Then FileCounter = 0 For Each folderIDX In PrimaryMergeFiles 'If folderIDX.Name = Worksheets("Tabl...

excel vba loops if-statement vlookup

You should also exit early.....

Stop reviewing SO code @ducky!

2

vba4all

16:35

:O

blog.tkacprow.pl/excel-multithreading-vba

need to see that i guess

"native VBA QuickSort procedure" - what's that? :)

StartTime, EndTime As Date makes Variant/ Date

well..

he got my 1 view and thats about it :P

ParallelSort.QuicksortParallel<int>(ar); - specially the <int> part...

Phrancis

Hey guys; I work with Excel a lot and would like to learn some useful/cool stuff you can do with VBA & Excel... How do I get started?

vba4all

16:53

what do you already know

Phrancis

I'm a wizard with Excel but I know nothing about VBA

vba4all

buy a VBA book first

?

learn the basics etc

Phrancis

Guess that's fair enough

vba4all

do you know any other programming languages?

Phrancis

SQL, a little PHP and web stuff mostly

enderland

16:56

first step is to find something annoying to do "manually" and then figure out how to automate it

Phrancis

Think this may be a decent place to start? excel-easy.com/vba/function-sub.html

vba4all

thats a first step to automation @enderland

first, start by understanding what a variable is

types of variables, limits, scopes

basic functions

enderland

people who go "I want to program!" without specific reasons generally quit not very long after

so find something which is painful you can make easier with code

having this room means you WILL be succsesful eventually

2

which gets you more excited about why it's bneeifical to learn coding

which is important long term

RubberDuck

17:17

@vba4all Still, I never considered the fact that long running processes could be sped up by multi-threading in .net and exposing it to vba via com.

@enderland I couldn't agree more. The easiest way to learn is to have a real problem to solve.

Phrancis

Sorry I got a long call

RubberDuck

<Configuration>
	<UserSettings>
		<ToDoListSettings>
			<MagicComments>
				<Item text="'NOTE:" priority="0" />
				<Item text="'TODO:" priority="1" />
				<Item text="'BUG:" priority="2" />
			</MagicComments>
		</ToDoListSettings>
	</UserSettings>
</Configuration>

Magic comment isn't a very good name, but that's what it is.... hmmm

Mat's Mug

17:33

^^ LOVE IT!!

RubberDuck

LOL. Then to hell with it. The configuration is going to have a MagicComments class.

=P

Mat's Mug

Actually could be <ToDoMarkers>

RubberDuck

That's not too bad.

And it should be a ToDoMarker not an Item.

Let's see what the 2nd Monitor has to say.

in The 2nd Monitor, 5 mins ago, by RubberDuck

I built a way to pick up comments and put them into a task list in vba. Because, ya know, COM I need to roll my own own configuration. This is what I came up with.

Mat's Mug

2nd Monitor doesn't get VBA...

RubberDuck

It is going to be a *.duckConfig file though.

Nah, but they get clean code and XML might get a bite.

Mat's Mug

17:37

That, yeah

*.rubberduck.config?

RubberDuck

Actually, it's going into the user's appdata folder anyway, so rubberduck.config works just fine.

Mat's Mug

Right

Phrancis

Can you write VBA for basically the entire MS Office suite?

Mat's Mug

yup

Alt+F11 in Excel > VBA; Alt+F11 in Word > VBA; Alt+F11 in Outlook > VBA; Alt+F11 in Access > VBA; Alt+F11 in PowerPoint > VBA; Alt+F11 in .. oh wait, doesn't work in OneNote :(

but you can write a COM add-in and load it into OneNote

(wonders WTF an add-in would do in OneNote)

Phrancis

17:58

Private Sub CommandButton1_Click()
Range("A1").Value = "Hello, World!"
End Sub

Gotta start somewhere :)

2

Mat's Mug

@RubberDuck I don't think the comment has to start with the marker to be picked up. Well, ReSharper picks up a todo item anywhere in a comment I think.. .will test that tonight

Phrancis

I tried renaming the button in Excel and got this, I'm sure this is a PICNIC error, how do I fix?

Mat's Mug

right click the button, properties

then change the (name) property to anything you want. needs to be a valid identifier though (must start with a letter)

Phrancis

OK, never knew all that stuff was there

The code doesn't care about Caption does it? Only the Name right?

Mat's Mug

(name) becomes an identifier in the code, so if it's called MyButton you can refer to it as MyButton in code

Caption is a property of the control, that the control uses to determine what text to display on it

Phrancis

18:08

Makes sense

Is Sub short for Subroutine?

Mat's Mug

sort of.

in standard modules (.bas) they're "procedures"; in class modules (.cls) they're "methods"

"subroutine" might refer to a GoTo or GoSub label inside a procedure or method

procedures that return a value are Functions

RubberDuck

@Mat'sMug Good point.

Mat's Mug

@Phrancis ugh. what's the T-SQL function to return the last inserted row ID again?

nevermind stackoverflow.com/questions/42648/…

Phrancis

@@IDENTITY

Or that yeah

Mat's Mug

SCOPE_IDENTITY

Phrancis

18:16

Not sure what the difference is, if any

Mat's Mug

> @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

Phrancis

Aha, makes sense

Mat's Mug

interesting. didn't know about this output clause: msdn.microsoft.com/en-us/library/ms177564.aspx

Phrancis

Neat. Pretty handy. Postgres has a similar RETURNS clause for functions but it's a bit of a PITA to use.

Mat's Mug

still figuring it out

RubberDuck

18:23

@Mat'sMug OUTPUT is the schiznit for setting variables with stored procedures.

Mat's Mug

dawatt?

ugh. "must declare the table variable @categoryId".. how do I output to a single little stupid variable?

declare @ts datetime;
set @ts = getdate();

declare @categoryId int;
insert into dwd.Categories (Code, Name, _DateInserted) output inserted._Id into @categoryId values ('CP', 'Suits', @ts);

RubberDuck

one sec

DECLARE @cycle char(8)
EXEC dbo.uspMAM_GetLastCompletedCycle @cycle OUTPUT

That's how it gets used in the calling code.

Mat's Mug

I don't get it

RubberDuck

Yeah. Here's the sproc

Mat's Mug

bah, I'll just declare a table variable and walk away happy

36

A: SQL Server Output Clause into a scalar variable

You need a table variable and it can be this simple. declare @ID table (ID int) insert into MyTable2(ID) output inserted.ID into @ID values (1)

RubberDuck

18:28

CREATE PROC [dbo].[uspMAM_GetLastCompletedCycle] (@cycle char(8) OUTPUT)
AS
SELECT @cycle = CYCLE
FROM dbo.sometable
WHERE D_CODE = 'CEND';

RETURN 0

Mat's Mug

boom. works.

declare @ts datetime;
set @ts = getdate();

declare @categoryId table (Id int);
insert into dwd.Categories (Code, Name, _DateInserted) output inserted._Id into @categoryId values ('CP', 'Suits', @ts);

RubberDuck

It's just like using a ref as an output variable really.

Mat's Mug

do I need to truncate the table variable when I do another Categories insert?

RubberDuck

@Mat'sMug Can you truncate a table variable??

Mat's Mug

I'd guess so

why not?

RubberDuck

18:43

I don't know. I guess I just never thought about it before.

Mat's Mug

I really like this output clause thingy

RubberDuck

Nothing like uploading a 73MB zip file to google drive over the VPN..... good grief it's taking forever.

Mat's Mug

lol

hey I downloaded VSC off my cell phone data plan yesterday

never consumed that much data

ever

RubberDuck

lol

Phrancis

19:02

Got an interesting challenge mentioned by one of my colleagues, can I run it by you guys?

vba4all

nope.

2

RubberDuck

@Phrancis What's up?

Phrancis

Our consumer relations group gets survey results from customers after a claim is closed. They are plain text emails that look like this:

> Name: John Smith
Email: [email protected]
Client: SomeClient
Contract Number: 123456
Service: Good
Comment: I called in for a repair and received satisfactory service.

They manually take this information and input it into an Excel document with columns for each field. Is it possible to write a macro or something like that to automate it between Outlook and Excel, or something along those line?

Mat's Mug

is the email subject somewhat standard?

like, how would a program identify an incoming email as "that's a form!"

Phrancis

Yeah it's something like "Survey result for SomeClient"

I think they have a dedicated inbox where they all go

RubberDuck

19:08

Could be an outlook add-in too. Let a person decide what is and isn't a form.

Mat's Mug

if an incoming email has a subject that starts with "Survey result for ", then you try to parse the email's body into some object (a class module /DTO basically) - if it succeeds, you have your form, if not, you don't

RubberDuck

I don't know much about outlook & vba, but I'm positive it's do-able.

Mat's Mug

yes

and you can even get the running instance of Excel ;)

(or start Excel)

(from within your Outlook VBA code)

RubberDuck

(Mug likes brackets)

Mat's Mug

(...)

{braces too}

RubberDuck

19:16

this is string typed pretty bad, but your class would look something like this.

Private Type TSurveyResults
    Name As String
    Email As String
    Client As String
    Contract As Long
    Service As String
    Comment As String
End Type

Private this As TSurveyResults

Public Property Get Name() As String
    Name = this.Name
End Property

Public Property Let Name(value As String)
    this.Name = value
End Property

' etc

and this should help you get a handle on excel

5

Q: Test if Excel is open without relying on catching an error

Getting a hook on an open instance of Excel is something I do all the time. I'm now questioning if I'm doing this the best way. I'm particularly concerned with the fact that I rely on the GetObject method to throw an error. I'd like to avoid it if possible. I have a Utils module that contains a ...

functions vba error-handling excel

The outlook stuff is all you @Phrancis. =;)-

Phrancis

lol ok thanks guys, I'll see what I can ~~break~~ do!

2

Phrancis

19:35

What am I missing? ^^ Originally had Set msg = Nothing which worked

RubberDuck

What line does it highlight?

Phrancis

Sub HelloWorldMessage()

Ah this works:

Sub HelloWorldMessage()
    Dim msg As Outlook.MailItem
    Set msg = Application.CreateItem(olMailItem)
    msg.Subject = "Hello World!"
    msg.Body = "This is a test"
    msg.Display
    Set msg = Nothing
End Sub

What's the meaning of Dim? I'm not a fan of truncated keywords lol

RubberDuck

It's short for Dimension

The important thing is it's how you declare local variables.

Mat's Mug

*local variables

RubberDuck

@ducky fix =)

Mat's Mug

19:44

Set is used for assigning a reference - for object variables

A String isn't an object in VBA ;)

Phrancis

Duh lol.

Mat's Mug

If you want to be old-school you can do Let msg =

But don't

Phrancis

If I'm not mistaken, a string is not an object in any language.

Is VBA kind of like Python in that a line break terminates a statement?

RubberDuck

@Phrancis String is an object in .Net =)

And Yes. Line break terminates a statement. Generally.

So does a colon though.

Dim str As String: str = "Hello World"

But don't do that either. =)

Phrancis

All keywords always in Title Case in VBA? What about vars, is it most common to use camelCase or snake_case?

Mat's Mug

19:55

@Phrancis standard is PascalCase for just about everything, but I like my locals (& parameters) in camelCase

the language isn't case sensitive though

so if you have Value in-scope and declare a value parameter, Value will turn into value and you'll curse at VBA

RubberDuck

camelCase for localVariables, PascalCase for Classes and Methods

And @Mat'sMug doesn't like this one @Phrancis, but I like to use SCREAM_CASE for constants.

Mat's Mug

heck, just do it the way you're comfortable with and post it on CR, and let the reviewers bun-fight over what's right :)

RubberDuck

hahahah. That ^

Phrancis

So... Dim str As String: str = "Hello World" is as good as DIM str AS STRING: str = "Hello World"

RubberDuck

Troll......

2

The IDE will fix the casing on the keywords.

Phrancis

20:49

Is it customary/practical to declare all your variables at the beginning of your Sub?

Or declare them as you need them later in the code?

Mat's Mug

better closer to usage, but keep in mind that a local variable is scoped to the entire procedure

RubberDuck

It's customary to declare them all at the beginning of the sub, but that doesn't make it right.

Phrancis

This is not highlighting anything so how would one tell what causes the error?

RubberDuck

Damn fine question.

1

Q: Debug.Assert behavior in MS Access runtime

In many compiled languages, calls to Debug.Assert, or their equivalent, are left out of the compiled production code for performance reasons. However, calls to Debug.Assert still appear to execute in the /runtime versions of MS Access applications. To test this, I added the following to my star...

vba debugging ms-access

Phrancis

It seems to work fine otherwise

Mat's Mug

21:05

@Phrancis put On Error GoTo ErrHandler at the top, and Exit Sub after the Next instruction; then add a ErrHandler: label and say Stop and then Resume - when it blows up you'll be able to step through the code and the Resume instruction will bring you to the line that blew up

note: never put Stop and Resume into production code

and never put Resume all by itself (without Stop) - otherwise it'll just become an infinite loop

Phrancis

21:20

@Mat'sMug something like this? Getting syntax error

Sub ContactName()
    Err GoTo ErrHandler
    Dim ContactsFolder As Folder
    Set ContactsFolder = Session.GetDefaultFolder(olFolderContacts)
    MsgBox ("Contacts found: " & ContactsFolder.Items.Count)

    Dim Contact As ContactItem
    For Each Contact In ContactsFolder.Items
        Debug.Print Contact.CompanyName
    Next
    Exit
ErrHandler:     myError
    Stop
    Resume
End Sub

Mat's Mug

Exit Sub, not just Exit ;)

and myError isn't in the code, right?

ooh, and On Error GoTo ErrHandler, not Err GoTo ErrHandler

On Error GoTo instructs VBA to jump to a specific label when a runtime error occurs

Phrancis

myError is highlighted

Mat's Mug

remove it

VBA thinks myError is a method call

On Error, not On Err ;)

Err is a special object (think global-scope singleton) that contains information about the current error

Phrancis

Aha. OK. It stopped on a specific company name, it has a ç character, I wonder if that's it

I resumed and it finished the query properly

I have multiple others with é, è, even î

@Mat'sMug Could it be a character set issue?

Phrancis

21:47

Beh, I changed it to a normal "c" and it's still getting hung up there

Phrancis

22:15

Actually seems to get hung up towards the end if I query other fields too... Hm.

Wait, question: Would Debug.Print display the record it is erroring on last before stopping, or would it display the one immediately before but not the bugged one?

Mat's Mug

it should print the last assigned value

Phrancis

I have one contact that's actually a distro list, it doesn't appear it is being listed in the Immediate, I wonder if that's what is causing the bug

Mat's Mug

are you getting an error?

it hits the error handler?

under the ErrHandler label, just before Stop, put Debug.Print Err.Description to get the error message displayed

Phrancis

"Type mismatch"

I did notice, the distro is actually not in the output at all, that's got to be it

Dim Contact As ContactItem

Mat's Mug

what line does Resume bring you to?

Phrancis

22:27

To Stop

Wait

Resume brings me to the end of the query in Immediate

Mat's Mug

huh??

you can't resume from the immediate pane

Phrancis

Maybe I don't understand the question ;)

Resume == Continue == F5 right?

Mat's Mug

ah, right.. F8 to step through code line by line - don't just F5 over Resume!

you did put Resume on the line after Stop right?

ErrHandler:
    Debug.Print Err.Description
    Stop
    Resume

Phrancis

Yeah

Sub ContactName()
    On Error GoTo ErrHandler

    Dim ContactsFolder As Folder
    Set ContactsFolder = Session.GetDefaultFolder(olFolderContacts)
    MsgBox ("Contacts found: " & ContactsFolder.Items.Count)

    Dim Contact As ContactItem
    For Each Contact In ContactsFolder.Items
        Debug.Print Contact.CompanyName
    Next
    Exit Sub

ErrHandler:
    Debug.Print Err.Description
    Stop
    Resume

End Sub

Mat's Mug

so when Stop gets highlighted, you do F8 once it takes you to Resume, then F8 again and that takes you to..... ?

Phrancis

22:31

Next

Mat's Mug

ah, my guess is that that mailing list is not a ContactItem object

Phrancis

The carries on to Debug.Print Contact.CompanyName

Hm. I think it's time for some good old SO

Unless you know a workaround off top of your head

Mat's Mug

Dim Contact As ContactItem
Dim Item As Variant
For Each Item In ContactsFolder.Items
    Set Contact = Item
    If Not Contact Is Nothing Then
        Debug.Print Contact.CompanyName
    End If
Next

IDK, just trying something

msdn.microsoft.com/en-us/library/office/…

Phrancis

It got hung up on Set Contact = Item this time :)

Mat's Mug

Dim Contact As ContactItem
Dim Item As Object
For Each Item In ContactsFolder.Items
    If Contact Is ContactItem Then
        Set Contact = Item
        Debug.Print Contact.CompanyName
    End If
Next

it's because .Items contains more than just ContactItem objects I'd say

but I've never really written VBA for outlook..

SO can help for sure

gotta go home, good luck!

Phrancis

22:37

OK seeya

Phrancis

22:52

0

Q: Outlook distribution list hanging up on contact items query

I was working from some tutorial on MSDN to learn to make some macros for Outlook. I have this subroutine that gets hung up with a Type mismatch error. Upon stepping through error handling after Stop and Resume it goes back to Next and finishes the query. Looking through the result set in Immed...

vba outlook-vba

enderland

23:36

@Phrancis try this:

> Dim Contact
For Each Contact In ContactsFolder.Items
debug.print typename(contact)
Debug.Print Contact.CompanyName
Next

I imagine it will show you why you get that error

Mat's Mug

@Phrancis did you try the last snippet I gave? Iterate Object items and check their type, then assign to a ContactItem if the object is a ContactItem?

enderland

woops. I just added an answer. lolz. Should have read up more :)

Mat's Mug

but when you encounter an Irange, VBA throws an error.

An Irange huh?

;)

enderland

Wonder if each "item which can be present in ContactList" inherits or implements an interface you could use instead of a variant ?

I'm pretty sure this isn't the case, i've had issues before related to this I think

Mat's Mug

23:51

I think for each apple over a Collection will only iterate apples in the collection. Weird that ContactList doesn't do that.

enderland

I've run into this before with outlook

enderland is pretty sure at least but can't document it to prove it :P

actually it's weird as heck that .Items returns different types of objects, there has to be some sort of interface... right?

Transcript for